Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
N-msft
Frequent Visitor

Use dynamic column names in calculation groups

Hi,

 

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

 

 TodaysValueYesterdaysValueDayBeforeYesterdayvalueMTD 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-Jan09-Jan08-JanJan (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 

 

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @N-msft ,

I created some data:

vyangliumsft_0-1718608476367.png

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())

vyangliumsft_1-1718608476369.png

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.

Thanks @v-yangliu-msft 

 

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.

Screenshot.png

 

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.