topic Use dynamic column names in calculation groups in Developer

Use dynamic column names in calculation groups

N-msft — Sat, 15 Jun 2024 06:39:02 GMT

Hi,

I am using calculation groups to show a matrix visual that contains measures on both axes, i.e., on rows & columns.

	TodaysValue	YesterdaysValue	DayBeforeYesterdayvalue	MTD for Curr Month
Total Sales
Total Profit

I have created Total Sales and Total Profit measures that calculate the sales and profit, and I have also created Todays Value, Yesterdays Value, and DayBefore Yesterday Value measures to put them in calculation items in the calculation group so that when I put the calculation group on the columns, it should calculate the Total Sales or Total Profit for that particular day/period in the value fields.

I am trying to get the measure names shown on column headers dynamically so that TodaysValue can be replaced with today's date, YesterdaysValue can be replaced with yesterday's date, DayBeforeYesterdayvalue can be replaced with the day before yesterday's date, and so on.

Assuming today's date is January 10, the expected output is:

	10-Jan	09-Jan	08-Jan	Jan (MTD)
Total Sales
Total Profit

I tried various ways but was unable to achieve this. Can someone guide me on how to get the expected results?

Thanks in advance.

@amitchandak, @rajendraongole1, @Ritaf1983 @Greg_Deckler

Re: Use dynamic column names in calculation groups

Anonymous — Mon, 17 Jun 2024 07:15:10 GMT

Hi @N-msft ,

I created some data:

Are you considering using measure directly to display the data you want:

Measure = var _day=TODAY() var _lastday=TODAY()-2 return IF( MAX('Table'[Date]) >= _lastday &&MAX('Table'[Date])<=_day, SUMX( 'Table',[Value]),BLANK())

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Liu Yang

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

Re: Use dynamic column names in calculation groups

N-msft — Sun, 07 Jul 2024 14:23:57 GMT

Thanks @Anonymous

I am using multiple measures i.e. Total Sales, Total Profits, etc. on Rows of the matrix along with a calculation group with the following calculation items :

TodaysValue
YesterdaysValue
DayBeforeYesterdayvalue
MTD for Curr Month

I am stuck at naming these calculation Items dynamically like -

TodaysValue should be Today's date.
YesterdaysValue should be yesterday's date
DayBeforeYesterdayvalue should be day before yesterday's date
MTD for Curr Month should be "Current Month's Name".

Hope I am able to explain it now..

Attached is the screesnhot as unable to upload .PBIX file.

Re: Use dynamic column names in calculation groups

foodd — Sun, 07 Jul 2024 12:14:25 GMT

Hello @N-msft, remember to adhere to the decorum of the Community Forum when asking a question.

Please provide your work-in-progress Power BI Desktop file (with sensitive information removed) that covers your issue or question completely in a usable format (not as a screenshot).

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523/highlight/true#M607150

This allows members of the Forum to assess the state of the model, report layer, relationships, and any DAX applied.

If your requirement is solved, please make THIS ANSWER a SOLUTION ✔️ and help other users find the solution quickly. Please hit the LIKE 👍 button if this comment helps you. Proud to be a Super User!