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,
I'm new to PowerBI and I'm trying to create a measure which calculates the sales for the same time period as last year (and for last week so I'd rather use the dateadd function).
I'm pulling through a lot of data so I'm summarised my table to week starting date and I haven't created the generic date table within PowerBI so I'm not sure if these are causing any problems.
When I do the formulas it's returning blank values (s/s below of dummy data).
I'd upload the pbix file but I'm not sure how. The formulas I'm using are:
Sales LY Test1 = CALCULATE(SUM(Sheet1[Sales]), SAMEPERIODLASTYEAR(Sheet1[Time Period]))
Sales LY Test2 = CALCULATE(SUM(Sheet1[Sales]), DATEADD(Sheet1[Time Period], -364, DAY))
Any help would be appreciated.
Solved! Go to Solution.
Hi @Anonymous ,
If you have a date table, your measure is correct, but there is a little detail, maybe you ignore, please refer the following steps,
1. Create a one-to-many relationship between your table and date table.
2. Then we create a measure like yours.
Sales LY Test1 = CALCULATE(SUM('Table'[Sales]), SAMEPERIODLASTYEAR('Date'[Date]))
3. At last we put the ‘Date’[date] to the visual, the result like this,
Or if you don’t have a date table, we can create a new measure to meet your requirement.
Sales LY Test 2 =
var _currentyear = MAX('Table'[Year])
var _lastdate = DATE(_currentyear-1,MONTH(MAX('Table'[Time Period])),DAY(MAX('Table'[Time Period])))
return
CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Time Period]=_lastdate))
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, I have the same problem and have tried multiple formulas but still showing blank.
I have a Table having KPI values, and another one is a calendar table. i am trying to show last year value but no luck
I have tried this:
Hi @Anonymous ,
If you have a date table, your measure is correct, but there is a little detail, maybe you ignore, please refer the following steps,
1. Create a one-to-many relationship between your table and date table.
2. Then we create a measure like yours.
Sales LY Test1 = CALCULATE(SUM('Table'[Sales]), SAMEPERIODLASTYEAR('Date'[Date]))
3. At last we put the ‘Date’[date] to the visual, the result like this,
Or if you don’t have a date table, we can create a new measure to meet your requirement.
Sales LY Test 2 =
var _currentyear = MAX('Table'[Year])
var _lastdate = DATE(_currentyear-1,MONTH(MAX('Table'[Time Period])),DAY(MAX('Table'[Time Period])))
return
CALCULATE(SUM('Table'[Sales]),FILTER(ALLSELECTED('Table'),'Table'[Time Period]=_lastdate))
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?
BTW, pbix as attached.
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , In such case use date table , example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Week year behind = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
Thanks for your response, I haven't had time to watch your webinar yet but I'm not sure I see the relevance of 'Date'[Date].
I've added the date table in but it's still not giving the answer I'd expect.
I've updated the formulas to:
Sales LY Test1 = CALCULATE(SUM(Sheet1[Sales]), SAMEPERIODLASTYEAR('Date'[Date]))
Sales LY Test2 = CALCULATE(SUM(Sheet1[Sales]), DATEADD('Date'[Date], -364, DAY))
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |