Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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 , please explore if measure like this can help
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
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
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.
User | Count |
---|---|
128 | |
109 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |