Structuring a rolling 12 month comparative analysis

[Walt1010]

I have a table of sickness leave events, including values for sickness date and sickness type. I would like to provide several charts and tables that compare the last 12 months data against the previous 12 months.  I have created a date table and linked it to the sickness leave table, and created a measure to identify the most recent sickness leave date. I have had a look at the DAX intelligence functions, and using them seems to be the way to go about this, butow do I about doing this please?

[mark_endicott · Accepted solution]

@Walt1010 - if you are looking to compare days against the exact point in time 12 months previously you can use DATEADD inside CALCULATE:

CALCULATE( [Measure], DATEADD( 'DateTable'[Date], -1, YEAR ) )

If you'd like to do something similar, but with a range of dates SAMEPERIODLASTYEAR might be more appropriate: https://dax.guide/sameperiodlastyear/ or DATESINPERIOD could help if this is more complex: https://dax.guide/datesinperiod/ 

If you'd like to compare against a rolling average of the last 12 months, you can either use a visual calculation: https://www.youtube.com/watch?v=McRGFB55_kI or a DAX measure: https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/ 

Ultimately which function you use will be determined by the type of comparison you are trying to do. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!

[v-echaithra]

 Hi @Walt1010 ,

May I ask if you have gotten this issue resolved?

If it is solved, please mark the helpful reply or share your solution and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.

Regards,
Chaithra.

[v-echaithra]

Hi @Walt1010 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra]

Hi @Walt1010 ,

We wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithra.

[v-echaithra · Accepted solution]

Hi @Walt1010 ,

Thank you for reaching out to Microsoft Community.

As per the information provided i understand that you want to compare sickness Leave over the Last 12 Months with the Previous 12 Months .

Make sure you have a Date Table with a continuous date range and a relationship exists between DateTable[Date] and Sickness[Sickness Date].

Sickness Count = COUNTROWS('Sickness')

Sickness Count L12M =

CALCULATE( [Sickness Count], DATESINPERIOD( 'DateTable'[Date], MAX('DateTable'[Date]), -12, MONTH ) )

Sickness Count P12M = CALCULATE( [Sickness Count], DATESINPERIOD( 'DateTable'[Date], EDATE(MAX('DateTable'[Date]), -12), -12, MONTH ) )

If this helped, please mark it as the solution so others can benefit too. And if you found it useful, kudos are always appreciated.

Thanks,
Chaithra E.

[mark_endicott · Accepted solution]

@Walt1010 - if you are looking to compare days against the exact point in time 12 months previously you can use DATEADD inside CALCULATE:

CALCULATE( [Measure], DATEADD( 'DateTable'[Date], -1, YEAR ) )

If you'd like to do something similar, but with a range of dates SAMEPERIODLASTYEAR might be more appropriate: https://dax.guide/sameperiodlastyear/ or DATESINPERIOD could help if this is more complex: https://dax.guide/datesinperiod/ 

If you'd like to compare against a rolling average of the last 12 months, you can either use a visual calculation: https://www.youtube.com/watch?v=McRGFB55_kI or a DAX measure: https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/ 

Ultimately which function you use will be determined by the type of comparison you are trying to do. 

If I answered your question please mark my post as the solution, it helps others with the same challenge find the answer!