Forum Discussion

N-msft's avatar
N-msft
Frequent Visitor
2 years ago

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 

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      N-msft
      Frequent Visitor
      2 years ago

      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.