Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Power BI Gurus,
This is how a simplified version of my table looks like - it has multiple years of data in the same format, one line per department for a given Period/ Scenario combination.
Is there a way to dynamically select Current year from a slicer so that if I select a year - it will be treated as Current Year (CY) and CY-1 will become Prior Year (PY)
Period | Scenario | Department | Revenue |
Dec-17 | Actual | department1 | 500 |
Mar-18 | Forecast | department1 | 550 |
Jun-18 | Forecast | department1 | 600 |
Sep-18 | Forecast | department1 | 500 |
Dec-18 | Forecast | department1 | 530 |
Dec-18 | Actual | department1 | 535 |
Mar-19 | Forecast | department1 | 580 |
Jun-19 | Forecast | department1 | 600 |
Sep-19 | Forecast | department1 | 620 |
Dec-19 | Forecast | department1 | 590 |
Dec-19 | Actual | department1 | 600 |
Mar-20 | Forecast | department1 | 620 |
Jun-20 | Forecast | department1 | 630 |
Sep-20 | Forecast | department1 | 620 |
Dec-20 | Forecast | department1 | 610 |
Dec-20 | Actual | department1 | 600 |
Dec-17 | Actual | department2 | 575 |
Mar-18 | Forecast | department2 | 633 |
Jun-18 | Forecast | department2 | 690 |
Sep-18 | Forecast | department2 | 575 |
Dec-18 | Forecast | department2 | 610 |
Dec-18 | Actual | department2 | 615 |
Mar-19 | Forecast | department2 | 667 |
Jun-19 | Forecast | department2 | 690 |
Sep-19 | Forecast | department2 | 713 |
Dec-19 | Forecast | department2 | 679 |
Dec-19 | Actual | department2 | 690 |
Mar-20 | Forecast | department2 | 713 |
Jun-20 | Forecast | department2 | 725 |
Sep-20 | Forecast | department2 | 713 |
Dec-20 | Forecast | department2 | 702 |
Dec-20 | Actual | department2 | 700 |
Dec-19 | Actual | department 3 | 345 |
Mar-20 | Forecast | department 3 | 357 |
Jun-20 | Forecast | department 3 | 362 |
Sep-20 | Forecast | department 3 | 357 |
Dec-20 | Forecast | department 3 | 351 |
Dec-20 | Actual | department 3 | 350 |
my final goal would be: I select Current Year = 2020
2019 | 2020 | |||||
PY | Forecast03 | Forecast06 | Forecast09 | Forecast12 | CY | |
department1 | 600 | 620 | 630 | 620 | 610 | 600 |
department2 | 690 | 713 | 725 | 713 | 702 | 700 |
department 3 | 345 | 356.5 | 362.25 | 356.5 | 350.75 | 350 |
If I select Current Year = 2019
2018 | 2019 | |||||
PY | Forecast03 | Forecast06 | Forecast09 | Forecast12 | CY | |
department1 | 535 | 580 | 600 | 620 | 590 | 600 |
department2 | 615 | 667 | 690 | 713 | 679 | 690 |
department 3 | 345 |
There might be department 3 scenario when new departments have no prior year activity.
Thank you!
Olga
Solved! Go to Solution.
@ogend , Create a date table, Have year there, and use that in the slicer. Use time intelligence for this year vs last year
In case you have a month in a given format, not date. Create date: a new column
Date = "01-" & [Period] // change data type to date and use
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Thanks a lot for the guidance, @amitchandak
I was able to build what i need based on the article you shared
Olga
@ogend , Create a date table, Have year there, and use that in the slicer. Use time intelligence for this year vs last year
In case you have a month in a given format, not date. Create date: a new column
Date = "01-" & [Period] // change data type to date and use
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
User | Count |
---|---|
106 | |
85 | |
81 | |
73 | |
71 |
User | Count |
---|---|
112 | |
101 | |
96 | |
74 | |
67 |