Monthly compliance

[Anonymous]

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...

[Greg_Deckler]

@Anonymous I can't figure out how you got your results from that sample data. 

[Anonymous]

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)

[v-easonf-msft · Accepted 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]

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.