Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Is there a way to create a meausure that only brings back the dates based on a specific year?
Or is there a way to match dates from this year to last year based on day of the week? ie yesterday was Wednesday 9/2. Same day of week last year was 9/4
Solved! Go to Solution.
@Turf03
Make sure you have a calendar table and it is linked to the sales table, also a column added for the years
Create the following two measures for 2019 and 2020
2020 Sales = Calculate ( Sum(Sales[Amount]), 'Calendar'[Year] = 2020 )
2019 Sales = Calculate ( SUM(Sales[Amount]), Dateadd('Calendar'[Date],-364,Day),'Calendar'[Year] = 2019)
You can have ot in a single chart to compare next to each other by day. Check the tools tip I added the date as well
Last Year Date = Calculate ( Sum('Calendar'[Date]), Dateadd('Calendar'[Date],-364,Day))
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi...I am really, really new to Power BI and DAX. I'm building an HR Dashboard that has headcount, # of new hires, OT rate, and turnover rate. On each dashboard, I have been asked to provide the data for 2020 and 2021 side by side. Because they want to see them in that fashion, I have created measures that are specific to each year, plus that will allow me to have the information ready to go for the requests that I know will be coming even moving into 2022.
That said, I cannot seem to filter the results so that the data is just for that year. I have tried everything I can think of, but I just don't know enough. For example, I have this measure created.
Any help woudl be appreciated!
Thanks
Erin
@Turf03 last year the same weekday is 364 days behind. Use a date table to get this.
Year Week behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Day))
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...
Does this return the value associated to the corresponding date or does it return just the date? I'm looking to return the actual date and not the value that occurred on that date
@Turf03
Use can use this measure to get the datw:
Same day Last Year = CALCULATE( MAX('Calendar'[Date]), Dateadd('Calendar'[Date],-364,Day))
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Ok that seems to work but to expand the question even more...
Say I am trying to create a table with the Sales values of each day and the corresponding day of the previous year.
How do I create the measures that show the dates of 2020, the corresponding day of 2019 and the a sales of each like the example below?
Furthermore, how would I create a line graph that shows the same data in a table plotting the sales data for each date and the dates of both years as the Axis?
2020 Date | 2020 Sales | 2019 Date | 2019 Sales |
9/3/2020 | 200 | 9/5/2019 | 300 |
@Turf03
Make sure you have a calendar table and it is linked to the sales table, also a column added for the years
Create the following two measures for 2019 and 2020
2020 Sales = Calculate ( Sum(Sales[Amount]), 'Calendar'[Year] = 2020 )
2019 Sales = Calculate ( SUM(Sales[Amount]), Dateadd('Calendar'[Date],-364,Day),'Calendar'[Year] = 2019)
You can have ot in a single chart to compare next to each other by day. Check the tools tip I added the date as well
Last Year Date = Calculate ( Sum('Calendar'[Date]), Dateadd('Calendar'[Date],-364,Day))
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Edit.....nevermind I figured it out!!!! Thanks for the great advice!!!!
@Fowmy excellent!
Can you show me the line graph set up?
I can't get it to line up like yours, its shows as continuous.
Also how should the Calendar table be linked?
@Turf03
You can get same weekday last year Amount like
Measure = Calculate ( Sum(Table[Value]), Dateadd(Calender[Date],-364,Day))
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group