LTM value on SelectedValue Based for Quarter & Month Hierarchy slicers

[Anonymous]

Performing a calculation to display revenue for the last 12 months, based on the formula below.

Revenue_LTM =

VAR _SelectedMonth =

IF(SELECTEDVALUE(dim_Time[Month])=BLANK(),

DATEVALUE(CALCULATE(FIRSTNONBLANK(dim_Time[Month],1),FILTER(ALL(dim_Time[Month]),LOOKUPVALUE(dim_Time[Month],dim_Time[Quarter],SELECTEDVALUE(dim_Time[Quarter]))))),

DATEVALUE(SELECTEDVALUE(dim_Time[Month]))

)

VAR _LastYear = DATEVALUE(_SelectedMonth) - 365

VAR _PastYearSales =

CALCULATE(

[Revenue_BM],

FILTER(

ALL(dim_Time),

dim_Time[Date]<=_SelectedMonth &&

dim_Time[Date]> _LastYear

)

)

RETURN

_PastYearSales

My date slicer is based on a quarter/month heirarchy and is the selected value. This works perfectly when a month is selected but when I actually select a quarter, the error thrown is a table of multiple values was supplied where a single value was expected.

Below is my date tabke with Quarter & Month being my slicers. when I select a quarter the last 12 months prior to the quarter revenue should be displayed.

Date               Year     QuarterofYear   MonthofYear            Quarter            Month           Month_Key             MonthYear
01/01/2018    2018           1                         1                       2018 Q1            Jan 2018        2018M1                     2018-01
02/01/2018    2018           1                         2                       2018 Q1            Feb 2018        2018M2                    2018-02
03/01/2018    2018           1                         3                        2018 Q1           Mar 2018       2018M3                    2018-03
04/01/2018    2018           2                         4                        2018 Q2           Apr 2018        2018M4                    2018-04
05/01/2018    2018           2                         5                        2018 Q2           May 2018       2018M5                    2018-05

[amitchandak]

@Anonymous , please explore if measure like this can help

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

[Anonymous]

Hey @amitchandak, This works perfect when a month is selected from my slicer. Small edit, from  your formula

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ])-1,-12,MONTH))

The problem is the quarter Selection, say I select 2021 Q2 in my data, it should show me Sales from 2020 Q2 up until 2021 Q1, which is last 12 months. With the logic above say I have only two months data in 2021 Q2 the max(date) or max(date) - 3 will not work.

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

Would you please use the following measure:

Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MIN('Date'[Date ]),-12,MONTH))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Thank you for the change, I initially thought of changing the max to minimum, but assumed the dates in period will capture all the selected as well as dates in period.