Moving Annual Total for previous 4 quarters

[msingh2019]

I need to compare two measures: Moving Annual Total (MAT) versus MAT for previous period. Please refer to sample data below. I have a slicer for Quarter Year so that the user can select the reference quarter for the MAT calculation. 

[Quarter Year]--[Units]

Q3 2019--------100

Q2 2019--------120

Q1 2019--------80

Q4 2018--------100

Q3 2018--------80

Q2 2018--------100

Q1 2018--------40

Q4 2017--------60

For example, if slicer selection = Q3 2019, [MAT] = Sum of units for Q3 2019 + Q2 2019 + Q1 2019 + Q4 2018 = 300 (rows in red font).

And [MAT Previous Period] = Sum of units for Q3 2018 + Q2 2018 + Q1 2018 + Q4 2017 = 280 (rows in blue font).

I tried using the following formulae - 

MAT = CALCULATE(SUM('Sales'[Units]),
DATESINPERIOD(DateDimension[DateKey],LASTDATE(DateDimension[DateKey]), -4, QUARTER)
) 

MAT Previous Period = CALCULATE([MAT Sales Units],SAMEPERIODLASTYEAR(DateDimension[DateKey]))

[MAT] computes correctly with the above formula. However [MAT Previous Period] is computing 'sum of units for Q3 2018 + Q2 2018 + Q1 2018' = 220. Somehow the formula is not able to include Q1 2017.  I have also tried changing the formula for [MAT Previous Year] using various options of DATESINPERIOD but with no success.  Please help.

[amitchandak · Accepted solution]

This should have worked. The only doubt I have is calendar does not have dates of 2017. But as you are able to display qtr. so seems like not possible.

 If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[amitchandak · Accepted solution]

This should have worked. The only doubt I have is calendar does not have dates of 2017. But as you are able to display qtr. so seems like not possible.

 If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[msingh2019]

@amitchandak After I updated the Start Date and End Date of the Calendar function, the MAT Previous Period measure is showing correct results! You were right. Thanks!!

[msingh2019]

Thanks @amitchandak. After seeing your response I checked my DateDimension table and discovered that the range definition of my CALENDAR function was incorrect. It indeed only had years from 2018 to 2019! In order to retest the MAT Previous Period, I changed the Start Date and End Date for the CALENDAR function and pointed them to the correct fact table. I can now see that the Datekey column of the DateDimension table is now showing years from 2017 all the way to 2023. However, all the time intelligence functions seem to have gone haywire after this change as other measures like YTD, YTD previous year etc. are no longer working in Power BI desktop. Any ideas about what could have happened? I know this is a different issue but I am unable to move forward with rechecking the MAT Previous Period measure without the time intelligence working in my model. Please note that I am connecting to an SSAS Tabular cube as my data source and the tables are in SSAS.

[amitchandak]

Please note all time intelligence functions need an end date to start calculating. So if don't have slicer it will go till the end of the calendar. Refer to my screenshot. I do not have data for 2020. So one screenshot does not show data. as soon I use any date slicer, it shows.

Few ppl use the end date of the calendar as today to handle this.

[msingh2019]

@amitchandak I need to create a visual which displays the MAT for rolling 4 quarters over Years on the x-axis. So the scenario is the user selects a quarter on the slicer (for example Q3 2019) and the chart needs to plot something like this - 

Slicer selection = Q3 2019

Any thoughts on what modifications I need to make to the MAT formula below and workaround to achieve a visualization like the above?

MAT = CALCULATE(SUM('Sales'[Units]),
DATESINPERIOD(DateDimension[DateKey],LASTDATE(DateDimension[DateKey]), -4, QUARTER)
) 

Thanks

[msingh2019 · Accepted solution]

I finally figured out the formula to achieve a rolling MAT and YTD over 4 quarters. My requirement is to provide 2 slicers for selecting the year and quarter, and a bar chart visualization to display the MAT and YTD for each year (on the x-axis).

Rolling MAT =
var CurrentSelection =
IF(
COUNTROWS(DISTINCT(ALLSELECTED(Quarter_Lookup[Quarter]))) = 1,
VALUES(Quarter_Lookup[End_Date]),
MAX(DateDimension[DateKey])
)
var CurrentDate = DATEVALUE(CurrentSelection & YEAR(MAX(DateDimension[DateKey])))
var PreviousDate = CurrentDate - 364
VAR Result =
CALCULATE(SUM('Sales Table'[Sales Value]),
DATESINPERIOD(DateDimension[DateKey],CurrentDate,-1,YEAR)
)
Return Result

Quarter_Lookup is a table that contains quarter codes Q1, Q2, Q3 and Q4 and an 'End_Date' column ('31/Mar/', '30/Jun/' and so on). This is the table that I use as a slicer for the user to select the quarter. I also have a Year slicer from which I retrieve the selected year, which I concatenate with the End_Date of the selected quarter. DateDimension is my Date table. 

I used a similar approach for rolling YTD as follows - 

Rolling YTD Sales Value =
var CurrentSelection =
IF(
COUNTROWS(DISTINCT(ALLSELECTED(Quarter_Lookup[Quarter]))) = 1,
VALUES(Quarter_Lookup[End_Date]),
MAX(DateDimension[DateKey])
)
var CurrentDate = DATEVALUE(CurrentSelection & YEAR(MAX(DateDimension[DateKey])))
var PreviousDate = DATE(YEAR(CurrentDate),1,1)
VAR Result =
CALCULATE(SUM('Sales Table'[MS Sales Value]),FILTER(ALLEXCEPT('Sales Table','Sales Table'[country_id],'Sales Table'[product]),'Sales Table'[DateKey]>=PreviousDate && 'Sales Table'[DateKey] <= CurrentDate)
)
Return Result

Both the formulas works perfectly!

[msingh2019]

@amitchandak any help will be really appreciated. Thanks.

[Anonymous]

Check to make sure your Data Type has not been changed. I've had that happen to me way too many time in the past.

[msingh2019]

Found out that I lost all the relationships after I recalculated the Datekey column based on new Start Date and End Date. I had to create all the relationships again and now the time intelligence functions are now working! Thanks