Dynamic Columns in Data Source

[lancea]

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?

[V-lianl-msft]

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.

[amitchandak]

@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/

[lancea]

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?