Forum Discussion
Modeling Solution?
I have a data file that I need to be able to filter by Fiscal Year. The information is sorted into columns like below. Any suggestions?
| ID | Current FY | Prior FY | Prior FY 2 | Prior FY 3 |
| 1001 | $ 10.00 | $ 10.00 | $ 30.00 | $ 5.00 |
| 1002 | $ 20.00 | $ 10.00 | $ 10.00 | $ 10.00 |
| 1002 | $ 70.00 | $ 10.00 | $ 20.00 |
Hi Stefber,
Thanks for the update. Glad to hear the unpivot tip helped and you were able to restructure your data! Just checking does this mean your issue is fully resolved?
Thanks & Regards,
Prasanna Kumar
8 Replies
- Use Power Query:
- Select the columns: Current FY, Prior FY, Prior FY 2, Prior FY 3
- Right-click → Unpivot Columns
- Rename the new columns to Fiscal Year Label and Amount
- Optional Enhancements:
- Add a calculated column to convert labels into actual fiscal years (e.g., if Current FY = 2025, Prior FY = 2024, etc.)
FY =
SWITCH(
[Fiscal Year Label],
"Current FY", 2025,
"Prior FY", 2024,
"Prior FY 2", 2023,
"Prior FY 3", 2022
)
- Create a slicer on FY or Fiscal Year LabelThanks Shahed, This seems like it would work, but it doesn't filter the data the way I need it to. I tried duplicating those columns and unpivoting the copies, but the attribute slicer isn't filtering. I want to be able to filter for Current FY or Prior FY. Any ideas?
Hi Stefber,
Right now your data is spread across separate columns for each fiscal year, which makes filtering difficult. The solution is to reshape it into a better format using Power Query. Instead of having “Current FY,” “Prior FY,” etc. as separate columns, you unpivot them so they become values in a single column called Fiscal Year, with another column holding the corresponding Amount. Once you do this, you can place the Fiscal Year field in a slicer and it will filter your chart correctly, showing only the years you select. The key is to use the new unpivoted fields in your visuals, not the old wide format columns.
Thanks & Regards,
Prasanna KumarHi Stefber,
Just following up to see if the Response provided by community members were helpful in addressing the issue.
If one of the responses helped resolve your query, please consider marking perticular response as the Accepted Solution. Feel free to reach out if you need any further clarification or assistance.
Best regards,
Prasanna KumarPrasanna Kumar, It was helpful thank you! It is a lage, flat data set that included many more comuns, so I separated it into three tables- not as elegant as I'd like, but the unpivot tip worked for what I needed. Thank you!
Hi Stefber,
Thanks for the update. Glad to hear the unpivot tip helped and you were able to restructure your data! Just checking does this mean your issue is fully resolved?
Thanks & Regards,
Prasanna Kumar
Hi Stefber,
Just following up to see if the Response provided was helpful in resolving your issue. Please feel free to let us know if you need any further assistance.
Best regards,
Prasanna Kumar
