cancel
Showing results for
Did you mean:

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

Frequent Visitor

## Return 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/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).

1 ACCEPTED SOLUTION
Community Support

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 11
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
Frequent Visitor

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.

Community Support

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.

Frequent Visitor

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.

Community Support

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.

Super User

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Question 1.pbix          thanks

Super User

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
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

Community Support

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.

Community Support

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.

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors