英文:

Attendance report with power BI

问题

我正在尝试构建一个关于出勤情况的Power BI报告。我是Power BI的新手，发现很难找到正确的逻辑。

我的数据看起来是这样的。

我需要的输出是这样的。

基本上，"The days attended" 列应该包含基于日期提到的出勤天数。例如，No 3 在5月10日首次出勤。因此，他的"出勤天数"列的值为1，然后他在13日再次出勤，所以他的值变成2。

能有人帮忙吗？我已经卡在这里很长时间了。

英文:

I am trying to build a Power BI report with respect to attendance. I am new to power BI and i am finding it hard to arrive at the correct logic.

So my data looks something like this

And I need an output like this

So basically, The days attended columns should contain the number of days attended based on the date mentioned. For example, No 3 attended on 10th of May first. So his value in days attended column is 1 and he attended again on 13th, so his value becomes 2

Can someone please help. I've been stuck with this for quite a while now

答案1

得分: 1

你可以使用以下方法来实现这个目标-

days_attended = 
calculate(
    count(your_table_name[unique_id]),
    filter(
        allexcept(your_table_name,your_table_name[unique_id]),
        your_table_name[Date] <= min(your_table_name[Date])
    )
)

以下是新列的代码-

days_attended_column = 

var curr_row_id  = your_table_name[unique_id]
var curr_row_date  = your_table_name[Date]

Return
calculate(
    count(your_table_name[unique_id]),
    filter(
        all(your_table_name),
        your_table_name[unique_id] = curr_row_id
            && your_table_name[Date] <=  curr_row_date
    )
)

这是示例输出-

<details>
<summary>英文:</summary>

You can achieve this with a measure as below-

days_attended =
calculate(
count(your_table_name[unique_id]),
filter(
allexcept(your_table_name,your_table_name[unique_id]),
your_table_name[Date] <= min(your_table_name[Date])
)
)

Here is the code for new column-

days_attended_column =

var curr_row_id = your_table_name[unique_id]
var curr_row_date = your_table_name[Date]

Return
calculate(
count(your_table_name[unique_id]),
filter(
all(your_table_name),
your_table_name[unique_id] = curr_row_id
&& your_table_name[Date] <= curr_row_date
)
)

here is the sample output-

[![enter image description here][1]][1]


  [1]: https://i.stack.imgur.com/vREfu.png

</details>



# 答案2
**得分**: 0

以下是您要翻译的内容：

"Someone will quickly jump in with DAX method, but if you wanted to do this in Powerquery/M, right click Unique ID, group by... provide a column name, use operation All Rows, and hit ok. In the formula bar then change the part starting with word **each** to instead be

each Table.AddIndexColumn(_, &quot;DaysAttended&quot;, 1, 1, Int64.Type), type table}})

Use arrows atop the new column to expand the extra columns. Change column types and re-sort as desired

Full sample code

let Source = Excel.CurrentWorkbook(){[Name=&quot;Table1&quot;]}[Content],
#&quot;Grouped Rows&quot; = Table.Group(Source, {&quot;Unique ID&quot;}, {{&quot;data&quot;, each Table.AddIndexColumn(_, &quot;DaysAttended&quot;, 1, 1, Int64.Type), type table}}),
#&quot;Expanded data&quot; = Table.ExpandTableColumn(#&quot;Grouped Rows&quot;, &quot;data&quot;, {&quot;Date&quot;, &quot;Index&quot;}, {&quot;Date&quot;, &quot;Index&quot;})
in  #&quot;Expanded data&quot;"

请注意，由于您要求只翻译代码部分，我已经省略了非代码的部分。

<details>
<summary>英文:</summary>

Someone will quickly jump in with DAX method, but if you wanted to do this in Powerquery/M, right click Unique ID, group by... provide a column name, use operation All Rows, and hit ok. In the formula bar then change the part starting with word **each** to instead be

     each Table.AddIndexColumn(_, &quot;DaysAttended&quot;, 1, 1, Int64.Type), type table}})

Use arrows atop the new column to expand the extra columns. Change column types and re-sort as desired

Full sample code


    let Source = Excel.CurrentWorkbook(){[Name=&quot;Table1&quot;]}[Content],
    #&quot;Grouped Rows&quot; = Table.Group(Source, {&quot;Unique ID&quot;}, {{&quot;data&quot;, each Table.AddIndexColumn(_, &quot;DaysAttended&quot;, 1, 1, Int64.Type), type table}}),
    #&quot;Expanded data&quot; = Table.ExpandTableColumn(#&quot;Grouped Rows&quot;, &quot;data&quot;, {&quot;Date&quot;, &quot;Index&quot;}, {&quot;Date&quot;, &quot;Index&quot;})
    in  #&quot;Expanded data&quot;


[![enter image description here][1]][1]


  [1]: https://i.stack.imgur.com/WZtWn.png

</details>