How to make table select new columns automatically

[danielcarreira1]

Hi all,

I have a report that each month will add a new column (pivoted data) with new figures, what i need is for the table the user sees to select automatically that new End of Month figures automatically.

Best Regards,

Daniel

[Anonymous · Accepted solution]

Hi @danielcarreira1 ,

According to your statement, I know your table should look like a pivoted data. It has a Keycolumn like category and many month columns with data for each month. You will add a new column with current month data by refresh and you want to show the latest data in your visual. Power BI couldn't catch column header directly. Power BI always calculates by column. So I suggest you to use "Unpivot Other Columns" function by select your Keycolumn to transform month value into one column.

Here is my sample.

New Table:

Create a measure, then create a Matrix visual, add this measure into visual level filter and set it to show items when value = 1.

Measure = 
VAR _LatestMonth = MAXX(ALL(Sheet3),Sheet3[Month])
RETURN
if(MAX(Sheet3[Month])=_LatestMonth,1,0)

Result is as below.

If you add a column in your data source, you can refresh data in Power BI Desktop, and matrix will show data in latest month.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.