Return previous year YTD value based on slicer selection

[cheryl0316]

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/2022	B	US	10
09/30/2022	B	US	10
10/31/2022	B	Japan	10
10/31/2022	A	Japan	10
12/31/2022	A	China	10
03/31/2023	A	Thailand	10
06/30/2023	A	Japan	10
09/30/2023	C	Vietnam	10
10/31/2023	A	US	10
10/31/2023	A	US	10
10/31/2023	A	US	10
10/31/2023	A	US	10
10/31/2023	B	Japan	10

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!

[Ashish_Mathur]

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]

Hi Ashish,

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

Date	Product	Country	Revenue
12/31/2021	A	China	10
03/31/2022	A	Mexico	10
06/30/2022	C	US	10
09/30/2022	B	Mexico	10
10/01/2022	B	Japan	10
10/15/2022	A	Japan	10
10/31/2022	A	China	10
12/31/2022	A	China	10
03/31/2023	A	Thailand	10
06/30/2023	A	Japan	10
09/30/2023	C	Vietnam	10
10/01/2023	A	Japan	20
10/25/2023	A	US	30

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.

[Anonymous]

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 ( MIN ( 'Table'[Date] ), -13 ) + 1,
        ALLSELECTED ( 'Table' ),
        QUARTER ( 'Table'[Date] )
            IN VALUES ( 'Date'[Date].[QuarterNo] )
                && YEAR ( 'Table'[Date] ) = YEAR ( 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]

Hi Yolo,

Thanks for the file. I copied your formula to my file but it doesn't work. I think the reason is I add one to many relationship between two tables. You may see the file I share below. 

[Anonymous · Accepted solution]

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.

[Ashish_Mathur]

Share the download link of the PBI file.

[cheryl0316]

Question 1.pbix          thanks

[Ashish_Mathur]

PBI file attached.

Hope this helps.

[cheryl0316]

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]

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.

[Anonymous]

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.