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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
ballist1x
Helper III
Helper III

Sameperiodlastyear not working for me....

Hi i need some help. i am calculating MTD, QTD and YTD and i want to see how this is being aniversarried Year over Year.

 

 

My MTD calculation looks like this;

 

MTD = TOTALMTD(SUM(REVENUE[Final Amount (£)]), CalendarKey[Date])

 

This works fine^^

 

the SAMEPERIODLAST year does not:

 

MTD STLY = CALCULATE([MTD], SAMEPERIODLASTYEAR(CalendarKey[Date]))
 
this is calcuating the TOTAL of the same Month Last year so that in my visulisation i am getting the actual MTD in 2019,
 
so for example, MTD is calculating 1/3/2019..15/3/2019 and in comparison MTD STLY is calculating the results for 1/3/2018..31/3/2018. i want it to calculate 1/3/2018..15/3/2018 so that i can do a comparison.
 
so obviously the values do not match up. i have a date key that is linked correctly via a single cross filter direction (both ways doesnt work due to a contogious selection error)
 
in my vislisation i want the columns:
 
Manufactuer / MTD / MTD LY / QTD / QTD LY / YTD / YTD LY
Pickeles / £30 / £25...
1 ACCEPTED SOLUTION
v-juanli-msft
Community Support
Community Support

Hi @ballist1x 

Test with a table with sales from 2018/1/1 to 2019/3/15,

Create measures

MTD sales = TOTALMTD(SUM(Sheet1[sales]),'calendar'[Date])

last date = LASTDATE(Sheet1[date])

MTD LY = TOTALMTD(SUM(Sheet1[sales]),DATEADD(FILTER(DATESMTD('calendar'[Date]),'calendar'[Date]<=[last date]),-1,YEAR))

1.png

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
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

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @ballist1x 

Is this problem sloved?

If not, please let me know.

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft
Community Support
Community Support

Hi @ballist1x 

Test with a table with sales from 2018/1/1 to 2019/3/15,

Create measures

MTD sales = TOTALMTD(SUM(Sheet1[sales]),'calendar'[Date])

last date = LASTDATE(Sheet1[date])

MTD LY = TOTALMTD(SUM(Sheet1[sales]),DATEADD(FILTER(DATESMTD('calendar'[Date]),'calendar'[Date]<=[last date]),-1,YEAR))

1.png

 

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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