Forum Discussion
cheryl0316
Helper II
Helper IIReturn previous year YTD value based on slicer selection
Below is an example of dataset. Date Product Country Revenue 12/31/2021 A China 10 03/31/2022 A Mexico 20 06/30/2022 C US 10 09/30/2022 B Mexico 10 09/30...
Hi cheryl0316
Try the following measure.
Measure2 = VAR a = CALCULATE ( EDATE ( MAX ( 'Table'[Date] ), -12 ), ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Date'[Date] ) ,CROSSFILTER('Table'[Date],'Date'[Date],None)) VAR b = CALCULATE(EOMONTH(MIN('Table'[Date]),-13)+1,ALLSELECTED('Table'),QUARTER('Table'[Date]) in VALUES('Date'[Date].[QuarterNo])&&YEAR('Table'[Date])=YEAR(MAX('Date'[Date])),CROSSFILTER('Date'[Date],'Table'[Date],None)) RETURN CALCULATE ( SUM ( 'Table'[Revenue] ), 'Table'[Date] >= b && 'Table'[Date] <= a,CROSSFILTER('Date'[Date],'Table'[Date],None ))Or
Measure3 = CALCULATE(SUM('Table'[Revenue]),DATESBETWEEN('Date'[Date],EOMONTH(MIN('Date'[Date]),-13)+1,EDATE ( MAX ( 'Table'[Date] ), -12 )))Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
cheryl0316Helper II
Helper IIHi Yolo,
Thanks for your reply. If I change 10/31/2023 to 10/25/2023 and select 2023 Q4, Last Year YTD will be 10/25/2022, but it shows the result of 10/31/2022
Hi cheryl0316
Try the following measure.
Measure2 =
VAR a =
CALCULATE (
EDATE ( MAX ( 'Table'[Date] ), -12 ),
ALLSELECTED ( 'Table' ),
'Table'[Date] <= MAX ( 'Date'[Date] )
)
VAR b =
CALCULATE (
EOMONTH ( MAX ( 'Table'[Date] ), -13 ) + 1,
ALLSELECTED ( 'Table' ),
'Table'[Date] <= MAX ( 'Date'[Date] )
)
RETURN
CALCULATE (
SUM ( 'Table'[Revenue] ),
FILTER ( 'Table', 'Table'[Date] >= b && 'Table'[Date] <= a )
)
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.