Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
cheryl0316
Frequent Visitor

Return previous year YTD value based on slicer selection

Below is an example of dataset.

 

cheryl0316_1-1707781985755.png

 

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

cheryl0316_6-1707340070474.png

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

 

cheryl0316_7-1707340095265.png

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!

1 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 )))

vxinruzhumsft_0-1708308312836.png

 

 

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.

View solution in original post

11 REPLIES 11
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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. 

 

cheryl0316_0-1707938017149.png

 

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

cheryl0316_1-1707938499703.png

 

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

cheryl0316_2-1707938600255.png

 

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

vxinruzhumsft_0-1707963046763.png

 

vxinruzhumsft_1-1707963058594.png

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.

 

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. 

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 )))

vxinruzhumsft_0-1708308312836.png

 

 

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.

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Question 1.pbix          thanks

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1708067825211.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
cheryl0316
Frequent Visitor

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

cheryl0316_0-1707837287395.png

 

cheryl0316_1-1707837376682.png

 

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.

 

v-xinruzhu-msft
Community Support
Community Support

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

vxinruzhumsft_0-1707803776615.png

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.

 

 

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.