Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
brownrice
Helper III
Helper III

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

1 ACCEPTED SOLUTION

Hi, @brownrice 

You can add a new calculated table.

New Table = CROSSJOIN(VALUES('Table1'[Users]),'Table2')

veasonfmsft_0-1664186739433.png

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

veasonfmsft_1-1664186989124.png

 

Best Regards,
Community Support Team _ Eason

View solution in original post

4 REPLIES 4
Greg_Deckler
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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, @brownrice 

You can add a new calculated table.

New Table = CROSSJOIN(VALUES('Table1'[Users]),'Table2')

veasonfmsft_0-1664186739433.png

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

veasonfmsft_1-1664186989124.png

 

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.

Helpful resources

Announcements
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.