Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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))
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |