Forum Discussion

cheryl0316's avatar
cheryl0316
Icon for Helper II rankHelper II
2 years ago
Solved

Return previous year YTD value based on slicer selection

Below is an example of dataset.

 

 

DateProductCountryRevenue
12/31/2021AChina10
03/31/2022AMexico20
06/30/2022CUS10
09/30/2022BMexico10
09/30/2022BUS10
09/30/2022BUS10
10/31/2022BJapan10
10/31/2022AJapan10
12/31/2022AChina10
03/31/2023AThailand10
06/30/2023AJapan10
09/30/2023CVietnam10
10/31/2023AUS10
10/31/2023AUS10
10/31/2023AUS10
10/31/2023AUS10
10/31/2023BJapan10

 

 

There are slicers for Year and Quarter

 

I have created one page that only display graphs for Product A.

 

 

I want to create a measure that can return previous year YTD value based on slicer selection.

 

For example,

 

I select 2023 Q4 on slicers, the last date is 10/31/2023. The measure should return $10 for previous YTD revenue (i.e. 10/01/2022 to 10/31/2022).

 

Any advice is appreciated. Thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

11 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi cheryl0316 

    You can create a data table

    Date = CALENDAR(DATE(2021,1,1),DATE(2023,12,31))

    Then create a measure.

    Measure 2 = var a = CALCULATE(EOMONTH(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))

    Output

    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.

     

     

  • cheryl0316's avatar
    cheryl0316
    Icon for Helper II rankHelper II
    2 years ago

    Hi 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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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.

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    2 years ago

    Hi,

    Create a Calendar Table with calculated column formulas of year, Month name, Month number and quarter.  Sort the Month name by the Month number.  Create a relationship (Many to One and Single) from the Date column of your data table to the Date column of the Calendar Table.  To your visuals/filters/slicers, drag any date dimension from the Calendar Table.  Write these measures

    R= sum(Data[Revenue])

    R in same quarter of PY = calculate([R],datesbetween(calendar[date],edate(max(calendar[date]),-13)+1,edate(max(calendar[date]),-12)))

    Hope this helps.

    • cheryl0316's avatar
      cheryl0316
      Icon for Helper II rankHelper II
      2 years ago

      Hi Ashish,

       

      Thanks for your reply. I have changed the data to do some tests.

       

      DateProductCountryRevenue
      12/31/2021AChina10
      03/31/2022AMexico10
      06/30/2022CUS10
      09/30/2022BMexico10
      10/01/2022BJapan10
      10/15/2022AJapan10
      10/31/2022AChina10
      12/31/2022AChina10
      03/31/2023AThailand10
      06/30/2023AJapan10
      09/30/2023CVietnam10
      10/01/2023AJapan20
      10/25/2023AUS30

       

      If I select 2023 Q4, the measure shows 10 for previous year YTD (i.e. 10/01/2022 to 10/25/2022) value which is correct. 

       

       

      If I select 2023 Q1-Q4, the measure should show 20 for previous year YTD (i.e. 01/01/2023 to 10/25/2022) value, but it shows 10 instead

       

      I try to use SAMEPERIODLASTYEAR with TOTALYTD, but the issue is TOTALYTD ignores the slicer.