cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
brianalva
Helper I
Helper I

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 @brianalva ,

 

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 IV
Super User IV

@brianalva , please explore if measure like this can help

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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 @brianalva ,

 

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

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors