cancel
Showing results 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

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
Community Support

Hi, @brownrice

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 4
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Helper III

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)

Community Support

Hi, @brownrice

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

Helper III

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.

Announcements

#### 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 Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors