Use dynamic column names in calculation groups

[N-msft]

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 

[Anonymous]

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.

[N-msft]

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.

 

[foodd]

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-...

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/1447...

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!