Last 12 months compared to 12 months prior to that period...!?

[Creative_tree88]

Last 12 Months V1 =

Hi guys - I have a problem (surprise, surprise!).  I'm trying to work out the current count of our activity (hospital so we count patient activity) for the past 12 months, and I need to compare it against the 12 months prior to that!  So, for example, rolling 12 months 09-21 to 08-22 compared to 09-20 to 08-21?

The problem I have is that our data is being updated daily, so if I just take current month and roll back 12 months, the beginning of the current month is going to be much lower in terms of patient count than the whole 12 months before that period (as that period will have FULL 12 months count).

I use a measure to calculate activity generally in my dashboard, as follows:

Count Activity = calculate(count('Activity & Demand'[AD_Event_Key]), 'Activity & Demand'[AD_Data]="Activity")+0

The date which refers to activity is a field called 'Activity & Demand'[AD_Event_DT} and so I need to somehow calculate the min and max for each period and compare it against the same period (albeit 12 months prior) for the previous period?

Any help is greatly appreciated!

[v-jingzhang]

Hi @Creative_tree88

Can you please tell us which dates should be the minimum date and maximum date for the current 12 month period that you want? For example, today is 2022/9/28, so your expected current period should be 2021/9/28 ~ 2022/9/27, or 2021/9/1 ~ 2022/8/31 (12 full calendar months)? Or some other date period?

Best Regards,
Community Support Team _ Jing

[AUDISU]

@Creative_tree88 

There is a DAX function "SAMEPERIODLASTYEAR". Try that one to get last year data.

Thank you.