Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

LTM value on SelectedValue Based for Quarter & Month Hierarchy slicers

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

1 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

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@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
Not applicable

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.

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
Not applicable

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.