Counting the number of modifications of a column

[Anonymous]

Hi,

I am a beginner with PowerBI and its tools. 

Let's say I have a list of members of a team, and I keep track of their presence status (present/absent). My file contains an id for each member, a column "updated at timestamp" as well as a column giving their current status. The list is updated daily, so some member's status can change from one day to the next.

What I would like to do is get the number of days the status was on "present" during a month (or a percentage of "present" during the month). 

Could you help me see how  I can do that, please ? 

[mahoneypat]

The presence icon is a dynamic thing and is not stored. I think the only way to get presence data would be with the Graph API (see link). And you'd have to make a Power Automate flow to check it at the desired frequency and store those values somewhere for Power BI to ingest. It should be doable but has is probably not a reliable way to check if someone is working or not.  You would be better off looking at actual activity instead of presence (# documents/records created/modified, etc.).

Get presence - Microsoft Graph v1.0 | Microsoft Docs

Pat

[Anonymous]

Thanks for your answer.

To make things clearer, the "presence/absence" status in the column is a value from my data source. It is not an icon. It is a string of characters ("present" or "absent", it could be "red" and "green" if I had chosen that instead) but it could as well be a boolean for instance (0 or 1). 

If I understand well, I would need to store the "status" everyday somewhere (would another table do ?) so that I can compare the new status with the stored one ? 

More generally (to make it more generic for other users who would have a similar issue), isn't there any way to check if a value has changed in PowerBI when you refresh the data ? 

I am also working on the same question with dates and I have thought about comparing today's date with the "last login" to count the numbers of days when the person has logged into their account. Would it be doable with a comparison column using the logic below : 

IF "last_login" is equal to "today's date" THEN nb_of_days = nb_of_days+1 ? 

Thanks for your help. 

[mahoneypat · Accepted solution]

I understand. My bad. To get Monthly analysis, you can either add a month column to your table or use a Date table (with a relationship on the Date column).

Creating a simple date table in DAX - SQLBI

To get the # of days Present, you could use a measure like this:

Days Present =
CALCULATE ( COUNT ( Data[Status] ), Data[Status] = "Present" )

To get the % of Days Present you could use one like this:

Pct Present =
VAR totaldays =
    COUNT ( Data[Status] )
VAR presentdays =
    CALCULATE ( COUNT ( Data[Status] ), Data[Status] = "Present" )
RETURN
    DIVIDE ( presentdays, totaldays )

Pat

[Anonymous]

I think I haven't understood the "month column or Date table" part. I followed the step from the link you proivided and I have a date table with several  columns (year, month, day of the month). I created a one-to-many relationship with the date of the last update from my initial table.

But the code returns the number of people who are present today, not the number of days each person was present during the month. Should I have a table for each person with the history of their presence/absence during the month ? Or how can this code apply to one person at a time ? 

Example : 

12/29/2021

MemberID	DateActualized	Status
1	12/27/2021	Present
2	11/14/2021	Present
3	12/07/2021	Absent
4	12/14/2021	Present

MemberID	Nb of days present
1	19
2	29
3	6
4	20

12/30/2021

The data is refreshed and now my datasheet shows : 

MemberID	DateActualized	Status
1	12/27/2021	Present
2	11/14/2021	Present
3	12/07/2021	Absent
4	12/30/2021	Absent

We can see that #1 was present on the 29th and is present on the 30th so the number of days present will increase by 1. However, #3 was absent on the 29th and is still on the 30th so the number will not increase. 

We can see that #2 has been present all December because the last DateActualized is in November, so the number of days present should be 30. 

We can see that #4 has changed his status on 12/30 and is now absent so the number of days present will not change on 12/30. 

Therefore the table should now be : 

MemberID	Nb of days present
1	20
2	30
3	6
4	20

Thanks.

[Anonymous]

Thanks for your reply !