Dynamic table column name

[StaceyGriffeth]

Is there a way to have the column name in a table visual dynamically update?  For example, I have a Trailing Twelve Month where it lists the current month and previous 11 month income statements.  The data automatically updates based on setting the current month in a measure.  However, I cannot get the month names to reflect in the column titles without manually changing them each month.  My workaround is to use a card for each column title with a measure which displays the appropriate month name.  The downside to this is that when the data refreshes as the current month is changed, the column widths change, and then the cards no longer line up.

[Anonymous]

Hi @StaceyGriffeth ,

Sorry to disturb you...

But did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.

Best Regards,
Eyelyn Qin

[Anonymous]

Hi @StaceyGriffeth ,

According to my understand ,you want to dynamically display data in last X months based on Slicer,right? 

You could use the following formula:

DateSlicer =
ALLSELECTED ( RevenueTable[Date] )

flagForSelected =
VAR _sele =
    SELECTEDVALUE ( DateSlicer[Date] )
VAR _diff =
    DATEDIFF ( _sele, SELECTEDVALUE ( RevenueTable[Date] ), MONTH )
RETURN
    IF ( _diff < 0 && _diff >= -6, 1 )

toRemoveOtherColumns =
SUM ( RevenueTable[Revenue] ) * [flagForSelected]

sumForRow =
SUMX ( RevenueTable, [toRemoveOtherColumns] )

My visualization looks like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[amitchandak]

@StaceyGriffeth , Not very clear. There is something to know dynamic column name.

refer: https://goodly.co.in/dynamic-column-names-power-query/

Or Dynamic Title : https://community.powerbi.com/t5/Desktop/Dynamic-title/td-p/378426

See if these can help

[vanessafvg]

do you leave it on its auto settings? Does this help at all https://www.youtube.com/watch?v=TYLKDPcvgK8

[StaceyGriffeth]

@vanessafvg I'm not clear on what you mean by auto settings?  The link is helpful for changing the title of the visual, but I'm looking for a way to change the name of the specific column in the table visual using the output of a measure.

[Greg_Deckler]

@StaceyGriffeth Hard to say exactly, can you share your PBIX or screen shots of what you are trying to achieve. Your measure formula, etc. 

[StaceyGriffeth]

@Greg_Deckler Basically I want the column title in the table visual to be the results of a measure.  That way the title will update as the value of the measure changes.

[Greg_Deckler]

@StaceyGriffeth In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

If you can share data, can be more specific.

[StaceyGriffeth]

@Greg_Deckler Here is the table:

The farthest right column, Cur, is the current month being reported (in this case June).  -1 means current month less 1 (May), and so on.  I have the following measures which set the current reporting month (updated monthly) and year (updated annually):

ReportMonth = 6

ReportYear = 2020

PriorYear = [ReportYear]-1

I am trying to automate this as much as possible, so I do not want to have to manually change the table column titles every time the month is advanced.  For example, this month, -1 is May, but next month -1 will be June.  I have measures which set what month and year is -1, -2, etc based on  ReportMonth and ReportYear:

TTMReportMonth-1 = SWITCH([ReportMonth], 1, 12, 2, 1, 3, 2, 4, 3, 5, 4, 6, 5, 7, 6, 8, 7, 9, 8, 10, 9, 11, 10, 12, 11)

ReportShortMonthCurrent-1 = SWITCH([ReportMonth],2, "JAN", 3, "FEB", 4, "MAR", 5, "APR", 6, "MAY", 7, "JUN", 8, "JUL", 9, "AUG", 10, "SEP", 11, "OCT", 12, "NOV", 1, "DEC")

TTMReportYear-1 = SWITCH([ReportMonth], 1, [PriorYear], 2, [ReportYear], 3, [ReportYear], 4, [ReportYear], 5, [ReportYear], 6, [ReportYear], 7, [ReportYear], 8, [ReportYear], 9, [ReportYear], 10, [ReportYear], 11, [ReportYear], 12, [ReportYear])

The month and year for -1, -2. etc are combined in the following:

TTM ReportMonthyear-1 = [ReportShortMonthCurrent-1]&" "&[TTMReportYear-1]

And can be displayed in a card and placed above the table column as the column title:

However, when data is refreshed with July as the current month, the column widths will change and the cards as titles will no longer line up with the table columns.

[Greg_Deckler]

@StaceyGriffeth Right, so as Disconnected Table Trick explains you create a disconnected table with the values you want in the columns. You use the measure to get the column value MAX('DisconnectedTable'[Column]). You can then use this in your measure to return the correct calculation. It's kind of warped thinking at first but it works. It is sort of the reverse of what you are doing now using a measure to compute the column value you want. Think that you already know which column you are in.

These things are difficult to mock up specifically without sample source data as text to copy and paste.

In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...

[StaceyGriffeth]

@Greg_Deckler I've been going over the disconnected table trick trying to figure out how it can solve my problem.  I see how it would isolate just the data I want to include in my table, but how does it solve the issue I have regarding the column name in the table visualization?  It seems like I would still have to update the column name each month to reflect the month/year it represents?