Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Monthly compliance

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

Need Help
  • v-easonf-msft's avatar
    v-easonf-msft
    3 years ago

    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

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        v-easonf-msft
        Community Support
        3 years ago

        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