Forum Discussion
How Show Prior months Value
Hello All,
I have situation where I have to show previous months data in line graph based on month selection in slicer
My table
fiscal year | Month | Year | Value_1 | Value_2 | Value_3 | Value_4 |
| 2020 | July | 2019 | 15 | 24 | 54 | 32 |
| 2020 | August | 2019 | 5 | 35 | 65 | 24 |
| 2020 | September | 2019 | 65 | 69 | 87 | 98 |
| 2020 | October | 2019 | 7 | 87 | 96 | 78 |
| 2020 | November | 2019 | 256 | 25 | 24 | 58 |
| 2020 | December | 2019 | 359 | 654 | 63 | 54 |
| 2020 | January | 2020 | 2458 | 255 | 54 | 65 |
| 2020 | February | 2020 | 236 | 35 | 25 | 32 |
| 2020 | March | 2020 | 25 | 302 | 37 | 12 |
| 2020 | April | 2020 | 26 | 336 | 32 | 14 |
| 2020 | May | 2020 | 630 | 370 | 27 | 18 |
| 2020 | June | 2020 | 682 | 404 | 23 | 100 |
| 2021 | July | 2020 | 734 | 438 | 18 | 20 |
| 2021 | August | 2020 | 786 | 473 | 13 | 58 |
| 2021 | September | 2020 | 837 | 507 | 8 | 69 |
| 2021 | October | 2020 | 889 | 541 | 3 | 75 |
| 2021 | November | 2020 | 941 | 575 | 35 | 95 |
| 2021 | December | 2020 | 993 | 609 | 69 | 35 |
| 2021 | January | 2021 | 1045 | 643 | 87 | 15 |
| 2021 | February | 2021 | 1097 | 678 | 25 | 72 |
| 2021 | March | 2021 | 1148 | 712 | 654 | 82 |
| 2021 | April | 2021 | 1200 | 746 | 255 | 9 |
| 2021 | May | 2021 | 1252 | 780 | 35 | 5 |
| 2021 | June | 2021 | 1304 | 814 | 300 | 86 |
Now I have a slicer for Fiscal Year and Month
slicer
Fiscal Year I have 2020 and 2021(lets say we select 2020)
Month: July 2019 to June 2020(the months are in order July to June)
Now If I select July in the month Slicer then i have to show only July Month values
| fiscal year | Month | Year | Value_1 | Value_2 | Value_3 | Value_4 |
| 2020 | July | 2019 | 15 | 24 | 54 | 32 |
Now If I select January in the month Slicer then i have to show July values to January
| 2020 | July | 2019 | 15 | 24 | 54 | 32 |
| 2020 | August | 2019 | 5 | 35 | 65 | 24 |
| 2020 | September | 2019 | 65 | 69 | 87 | 98 |
| 2020 | October | 2019 | 7 | 87 | 96 | 78 |
| 2020 | November | 2019 | 256 | 25 | 24 | 58 |
| 2020 | December | 2019 | 359 | 654 | 63 | 54 |
| 2020 | January | 2020 | 2458 | 255 | 54 | 65 |
Can anyone help me with this situation
Anonymous
You should first have a calendar in the first place. And to get the last n month data, please refer to the solutions in the following posts.https://stackoverflow.com/questions/49146565/show-last-3-months-from-selected-month-in-power-bi
https://community.powerbi.com/t5/Desktop/DAX-formula-to-return-data-for-last-6-months/td-p/50455
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
Anonymous , you can use datesytd and totalytd they take year-end date. This just for reference where year start and end.
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"6/30")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"6/30")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"6/30")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"6/30")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"6/30")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))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.Anonymous
You should first have a calendar in the first place. And to get the last n month data, please refer to the solutions in the following posts.https://stackoverflow.com/questions/49146565/show-last-3-months-from-selected-month-in-power-bi
https://community.powerbi.com/t5/Desktop/DAX-formula-to-return-data-for-last-6-months/td-p/50455
Paul Zheng _ Community Support Team
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
