Hi all - I've attached a sample pbix file which is where I've started...and ended at the moment. My date is in the table called 'Activity & Demand - TEST FILES FOR APP'. I have a Calendar / Date table (unconnected at the moment).
In my main data, I have essentially two dates I use to measure activity and demand. I measure activity using the field 'AD_Event_DT' and I measure demand using the field 'AD_Request_Date'. I can perform a simple count (as shown in the pbix link in this message) for activity and demand. Produce a graph etc...but what I really need to do is start producing measures which show, for example, the past 12 months activity, the past 12 months demand, then compare against the previous 12 months activity or demand period. The problem I have, is the calendar table needs to recognise that for activity, the min / max dates based on AD_Event_DT will be different to the min / max dates for AD_Request_Date. I only want to use the field AD_Event_DT for activity, and only the AD_Request_Date when measuring demand...!
So, how can I set the calendar / date table up to be able to allow me to compare two periods of time, and all the tricks you guys are so good at for comparing other time intervals, for activity and demand, as per my attached sample data?
I'd really appreciate some practical help with this as I've tried to do this many times, and just can't get it to produce anything near what I need in terms of measures - especially interested in time period to time periods, measures.
These two formulas are what I've tried to use, to produce some time period comparisons - the first (rolling 12) works OK, but the second (Rolling 12 before 12) only shows when nothing is selected - if I try to select a modality (using the AD_Modality) field, I get an error message - something about contiguous date fields...!?
I use AD_Event_Key to count with, and I somehow need the date / calendar table to update with respective dates every time it detects new dates have been added after a data refresh. This is all a bit mind boggling to me.
Rolling 12 = CALCULATE(count('Activity & Demand'[AD_Event_Key]), 'Activity & Demand - TEST FILES FOR TEST APP '[AD_Data]="Activity",DATESINPERIOD('Activity & Demand - TEST FILES FOR TEST APP '[AD_Event_DT],ENDOFMONTH('Activity & Demand - TEST FILES FOR TEST APP'[AD_Event_DT]),-12,MONTH))
Rolling 12 before 12 = CALCULATE(count('Activity & Demand - TEST FILES FOR TEST APP'[AD_Event_Key]), 'Activity & Demand - TEST FILES FOR TEST APP '[AD_Data]="Activity", DATESINPERIOD('Activity & Demand - TEST FILES FOR TEST APP'[AD_Event_DT],ENDOFMONTH(DATEADD('Activity & Demand - TEST FILES FOR TEST APP'[AD_Event_DT]-12,MONTH)),-12,MONTH))
Many thanks guys.
Link to File: https://drive.google.com/file/d/1MEYWTxmfmLt5QpnCn4g1wG7XwFy4ayzP/view?usp=sharing
