Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All,
When I try to use SAMEPERIODLASTYEAR to get MTD of previous year, it returns entire months sales sum, rather I was looking for only till current year date sum.
For example:
Current data is till 10th April I need to calculate sum of sales
MTD of current year (10th April 2018) and MTD (Till 10th April 2017)
Thanks!
Is this resolved? I am facing the same issue.
Hi @Anonymous,
I had a similar problem, with YTD as well as MTD, the measures for the previous were calculating on the full month and not until the current date.
The first thing that I did is limit my date table to always be up until the latest date in my fact table. Then the below measures will work.
Here are the YTD and PYTD
YTD UNITS = CALCULATE([TOTAL UNITS],DATESYTD('CALENDAR'[Date])) PYTD UNITS = CALCULATE([YTD UNITS],DATEADD(LASTDATE('CALENDAR'[Date]),-1,YEAR))
Here are the MTD and PMTD
MTD UNITS = CALCULATE([TOTAL UNITS],DATESMTD('CALENDAR'[Date])) PMTD UNITS = CALCULATE([MTD UNITS],DATEADD(LASTDATE('CALENDAR'[Date]),-1,MONTH))
The TOTAL UNITS measure is straightforward SUM
TOTAL UNITS = SUM('FACT'[UNITS])
I hope this helps you, let me know.
Hi nikhilkabbin,
Did you find a solution for this? I’m having the same problem. I also noticed this occurs for the DATEADD function too.
To test, I created a data model in PowerPivot containing only a date table starting January 1, 2016 and ending May 4, 2018.
The formula: Last Date In Table:=LASTDATE('Date Table'[Date]) returned the May 4, 2018, as expected
This month to date type formula below also returned the May 4, 2018:
Last Date in selected period:=CALCULATE(LASTDATE('Date Table'[Date]),DATESBETWEEN('Date Table'[Date],DATE(2018,5,1),DATE(2018,5,4)))
Why does adding SAMEPERIODLASTYEAR return the May 31 2017 instead of May 4, 2017 in the formula below?
Last Date in selected period year ago:=CALCULATE(LASTDATE('Date Table'[Date]),SAMEPERIODLASTYEAR(DATESBETWEEN('Date Table'[Date],DATE(2018,5,1),DATE(2018,5,4))))
I am hoping to avoid writing formulas like this
Month Sales To Date:=
Var
End_Date_This_Year = LASTDATE('Date Table'[Date])
Return
Var
End_Date_Last_Year = DATE(
YEAR(End_Date_This_Year)-1,
MONTH(End_Date_This_Year),
DAY(End_Date_This_Year)
)
Return
CALCULATE(
SUM('Sales Table'[Sales]),
DATESBETWEEN(
Date Table'[Date],
STARTOFMONTH(End_Date_Last_Year)
End_Date_Last_Year
)
)
Thanks!
Hi @nikhilkabbin,
You could create a date table with the formula below first.
Table = CALENDAR(DATE(2017,1,1),TODAY())
Then create the measure like below.
Measure = CALCULATE(SUM(Amount[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))
'Table '[Date] is a column from another table I created and create the relationship between the two tables.
The result output is below.
You could have a reference of this pbix file.
If you need additional help please share some data sample and expected output.
Best regards,
Dale
Thanks @v-jiascu-msft I used "SAMEPERIODLASTYEAR" it is working for monthly data but not for daily data. What I mean is "SAMEPERIODLASTYEAR" is calculating for entire month previous period not only till date current year.
I will share Sample report if this is not clear.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
97 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |