Forum Discussion
Dynamic Columns in Data Source
Suppose I have the following data source in Excel:
I have a problem retrieving data for the latest month in my visuals, because as the columns are re-arranged depending on which month it is. They are separated into actual sales and forecast. So for example, next month it will become:
Which breaks my visuals when I do a data refresh, since it no longer dispalys the latest month (which should now be Apr-21 Sales)
What are the workarounds for this?
3 Replies
lancea , I think you should first unpivot. Split and separate measure and month year (Split on last occurrence of space. an then pivot the measures again.
if need create date using month year
date = "01-" & [Month year] // change data type to date.
Then you find the latest month
max flag =
if([Date] = max([Date]), "Current Month", [Month Year])
You can filter of the current month
refer these if needed
https://radacad.com/pivot-and-unpivot-with-power-bi
https://www.tutorialgateway.org/how-to-split-columns-in-power-bi/
Hello, thank you for the info. My question is how do I know which column to unpivot? Since the latest column is always changing. Or do I unpivot all columns starting from the Jan-21?
Hi lancea ,
Based on your description,do you want to display the latest month's data after each refresh?
Is the data model exactly the same as the picture you provided? Does the date column exist in the data model?
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
