Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello great people of this community. Please hear my tale of what I'm trying to do:
Date | User | Balance |
29/10/2017 | 1 | 10 |
29/10/2017 | 2 | 15 |
29/10/2017 | 3 | 5 |
30/10/2017 | 1 | 15 |
30/10/2017 | 3 | 10 |
31/10/2017 | 1 | 20 |
31/10/2017 | 2 | 5 |
31/10/2017 | 3 | 20 |
Date | Active_Total | Non_Active_Total |
29/10/2017 | 30 | 0 |
30/10/2017 | 25 | 15 |
31/10/2017 | 45 | 0 |
Solved! Go to Solution.
Hi,
Here is the PBI desktop version.
Hope this helps.
Hi @stijn977
Use this MEASURE for Non Active Total
Non Active Total = VAR Currentday = VALUES ( TableName[Date] ) RETURN IF ( HASONEVALUE ( TableName[Date] ), SUMX ( EXCEPT ( ALL ( TableName[User] ), CALCULATETABLE ( VALUES ( TableName[User] ), TableName[Date] = Currentday ) ), VAR mydate = CALCULATE ( LASTNONBLANK ( TableName[Date], 1 ), FILTER ( ALL ( TableName[Date] ), TableName[Date] < Currentday ) ) RETURN CALCULATE ( LASTNONBLANK ( TableName[Balance], 1 ), FILTER ( ALL ( TableName[Date] ), TableName[Date] = mydate ) ) ) )
Amazing, I can’t wait to try this! Will definitely let you know the result!
Try this Measure
Non Active Total = VAR Priorday = PREVIOUSDAY ( VALUES ( Table1[Date] ) ) VAR Currentday = VALUES ( Table1[Date] ) RETURN IF ( HASONEVALUE ( Table1[Date] ), CALCULATE ( SUM ( Table1[Balance] ), EXCEPT ( ALL ( Table1[User] ), CALCULATETABLE ( VALUES ( Table1[User] ), Table1[Date] = Currentday ) ), Table1[Date] = Priorday ) )
Hello, thank you for your suggestion. However we can not be sure that we only need to go back one day to find the balance of the users not active today. This could be the previous day, or a week or month ago. So it need to be something along the lines of "get the balance for that users (who are not active today) , where the MAX(date) is < the day you're calculating for, and add up all the balances you've found for all these users that were not active.
But thank you for taking the time, I did not know about the EXCEPT functionality, so I might try and experiment along those line! thanks
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |