Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!See when key Fabric features will launch and what’s already live, all in one place and always up to date. Explore the new Fabric roadmap
Stumped as the best way to approach this problem being a relative beginner with Power BI....
With the below example data (dd/mm/yyyy), where users are supposed to report every 50 days:
Users | Report Date | Days between reports | ||
John | 02/11/2020 | 56 | ||
John | 07/09/2020 | 25 | ||
John | 13/08/2020 | 83 | ||
John | 22/05/2020 | 75 | ||
John | 08/03/2020 | 25 | ||
John | 12/02/2020 | 42 | ||
John | 01/01/2020 | null | ||
Betty | 09/10/2020 | 25 | ||
Betty | 14/09/2020 | 91 | ||
Betty | 15/06/2020 | 43 | ||
Betty | 03/05/2020 | 59 | ||
Betty | 05/03/2020 | 62 | ||
Betty | 03/01/2020 | null |
How could I display what % of users were complying with the 50 day rule at the beginning of each month?
Clearer explanation of expected results:
So on 01/02/2020 its been 31 days since John reported (last report 01/01/2020) and 29 days since Betty reported (last report 03/01/2020). Both durations are less than 50 days therefore 100% compliance on 01/02/2020.
The next month beginning 01/03/2020 its been 18 days since John last reported (last report 12/02/2020) and 58 days since Betty last reported (last report 03/01/2020). Therefore Betty has not reported on time meaning 50% compliance on 01/03/2020.
The next month beginning 01/04/2020 its been 24 days since John reported (last report 08/03/2020) and 27 days since Betty reported (last report 05/03/2020). Both durations are less than 50 days therefore 100% compliance on 01/04/2020.
On 01/05/2020 is has been 54 days since John last reported (last report 08/03/2020) and 57 days since Betty last reported (last report 05/03/2020). Both durations are greater than 50 days therefore 0% compliance.
Results should be:
01/02/2020 both users were compliant therefore = 100%
01/03/2020 John was compliant but Betty was not compliant = 50%
01/04/2020 both users were compliant therefore = 100%
01/05/2020 Neither John nor Betty was compliant therefore = 0%
01/06/2020 both users were compliant therefore = 100%
01/07/2020 both users were compliant therefore = 100%
01/08/2020 John was not compliant but Betty was compliant therefore = 50%
01/09/2020 John was compliant but Betty was not compliant therefore = 50%
01/10/2020 both users were compliant therefore = 100%
01/08/2020 John was not compliant but Betty was compliant therefore = 50%
No problem manipulating the data in Power Query if it helps...
Solved! Go to Solution.
Hi, @Anonymous
You can add a new calculated table.
New Table = CROSSJOIN(VALUES('Table1'[Users]),'Table2')
Then try measure formula like:
last report date =
CALCULATE (
MAX ( 'Table1'[Reported Date] ),
FILTER (
ALL ( 'Table1' ),
'Table1'[Reported Date] <= MAX ( 'New Table'[Start Date] )
&& 'Table1'[Users] = MAX ( 'New Table'[Users] )
)
)
diff = DATEDIFF( 'New Table'[last report date],MAX('New Table'[Start Date]),DAY)
Result =
VAR a =
CALCULATE (
DISTINCTCOUNT ( 'New Table'[Users] ),
FILTER (
'New Table',
'New Table'[Start Date] = MAX ( 'New Table'[Start Date] )
&& [diff] <= 50
)
)
VAR b =
DISTINCTCOUNT ( 'New Table'[Users] )
RETURN
a / b + 0
Best Regards,
Community Support Team _ Eason
@Anonymous I can't figure out how you got your results from that sample data.
Hi Greg,
So on 01/02/2020 its been 31 days since John reported (last report 01/01/2020) and 29 days since Betty reported (last report 03/01/2020). Both durations are less than 50 days therefore 100% compliance on 01/02/2020.
The next month beginning 01/03/2020 its been 18 days since John last reported (last report 12/02/2020) and 58 days since Betty last reported (last report 03/01/2020). Therefore Betty has not reported on time meaning 50% compliance on 01/03/2020.
The next month beginning 01/04/2020 its been 24 days since John reported (last report 08/03/2020) and 27 days since Betty reported (last report 05/03/2020). Both durations are less than 50 days therefore 100% compliance on 01/04/2020.
On 01/05/2020 is has been 54 days since John last reported (last report 08/03/2020) and 57 days since Betty last reported (last report 05/03/2020). Both durations are greater than 50 days therefore 0% compliance. (i got this wrong in my previous post apologies)
Hi, @Anonymous
You can add a new calculated table.
New Table = CROSSJOIN(VALUES('Table1'[Users]),'Table2')
Then try measure formula like:
last report date =
CALCULATE (
MAX ( 'Table1'[Reported Date] ),
FILTER (
ALL ( 'Table1' ),
'Table1'[Reported Date] <= MAX ( 'New Table'[Start Date] )
&& 'Table1'[Users] = MAX ( 'New Table'[Users] )
)
)
diff = DATEDIFF( 'New Table'[last report date],MAX('New Table'[Start Date]),DAY)
Result =
VAR a =
CALCULATE (
DISTINCTCOUNT ( 'New Table'[Users] ),
FILTER (
'New Table',
'New Table'[Start Date] = MAX ( 'New Table'[Start Date] )
&& [diff] <= 50
)
)
VAR b =
DISTINCTCOUNT ( 'New Table'[Users] )
RETURN
a / b + 0
Best Regards,
Community Support Team _ Eason
Hi v-easonf-msft,
Thank you for your efforts, you certainly solved the issue I posed. Unfortunately now that I have applied it to my actual dataset it seems to be taking an age to calculate. I presume this is due to the size of the dataset combined with the approach taken. But nevermind, you resolved the question in the OP.
User | Count |
---|---|
88 | |
68 | |
67 | |
58 | |
53 |
User | Count |
---|---|
40 | |
38 | |
34 | |
32 | |
28 |