Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I'm trying to use DAX to display year-over-year changes. In my situation, I don't think I can use time intelligence functions because my data doesn't exactly correspond to calendar months and days.
This is my source data. Each fiscal period is a month long, but starts on the 26th instead of the 1st. The business day indicates how many business days (non-holiday weekdays) have been completed during this fiscal period. The cumulative amount is the sum from the beginning of the period to the current business day.
My goal is to create a table like the following, where a user can select a fiscal period via slicer (example: 201803) and see, for each business day, the current year's amount and last year's amount for the same period and business day. You can get last year's fiscal period by subtracting 100 from the current fiscal period: (201803 - 100 = 201703). Note that in some cases, the months will have a different number of business days.
I also have a calendar of dates in a format similar to the following (I simplified these data views):
Thus far, I haven't figured out how to do this. Here was one of my attempts:
Amount Last Year = VAR this_period = MAX(Calendar[Period]) VAR this_business_day = MAX(Calendar[Business_Day]) RETURN CALCULATE( SUM('Data'[Cumul_Amount]), FILTER(Calendar, Calendar[Period]=this_period-100 && Calendar[Business_Day] = this_business_day ) )
Solved! Go to Solution.
Hi @Anonymous
You may use ALLSELECTED Function (DAX). You may try below measure:
Amount Last Year = VAR this_period = MAX ( Calendar[Period] ) VAR this_business_day = MAX ( Calendar[Business_Day] ) RETURN CALCULATE ( SUM ( 'Data'[Cumul_Amount] ), FILTER ( ALLSELECTED ( 'Calendar' ), Calendar[Period] = this_period - 100 && Calendar[Business_Day] <= this_business_day ) )
Regards,
Cherie
Hi @Anonymous
You may use ALLSELECTED Function (DAX). You may try below measure:
Amount Last Year = VAR this_period = MAX ( Calendar[Period] ) VAR this_business_day = MAX ( Calendar[Business_Day] ) RETURN CALCULATE ( SUM ( 'Data'[Cumul_Amount] ), FILTER ( ALLSELECTED ( 'Calendar' ), Calendar[Period] = this_period - 100 && Calendar[Business_Day] <= this_business_day ) )
Regards,
Cherie
User | Count |
---|---|
140 | |
113 | |
104 | |
76 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |