Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Dynamically change measure header name based on criteria

Hi all,

 

I have measures to calculate the total sales amount for current month, previous month, previous 2 months.

I need a way to dynamically change the header names of my matrix to display what those month names would be.

Current = measure

previous = measure

previous 2 = measure

 

Example of my measure DAX:

Previous 2Month = calculate([Sales Amount], PARALLELPERIOD('Date'[Date],-2,MONTH))

 

I want an output like this:

 

 

Need Help

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Super User
    4 years ago

    Hi Anonymous ,

     

    You should just be able to use a month name field from your calendar table for the column field in your matrix, then put a measure in there that's just:

    _sales = SUM(yourTable[Sales])

     

    The trick will be applying a visual-level filter that will only display current -2 months. For this, I would add a relative month field into your calendar table, something like this:

    (Date.Year([date]) * 12 + Date.Month([date])) - (Date.Year(DateTime.LocalNow()) * 12 + Date.Month(DateTime.LocalNow())

    Then use this to filter the visual to 0, -1, and -2.

     

    Pete

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Okay I have a potential different route based on current DAX.

       

      so I have a measure that calculates an amount with some filters. Within this measure, can I tell it to change the header name to the appropriate month? I have a column in my calendar table called "MonthName" that displays the months name.

       

      (this measure calculates an amount based on other parameters to put within 30/60/90 day buckets)

      _Grouped Amount = CALCULATE([_Open Amount],FILTER('RM20101 - Receivables',COUNTROWS(FILTER('Aged Groups',[_#Days] >='Aged Groups'[Min] && [_#Days] <= 'Aged Groups'[Max]))))

       

      • BA_Pete's avatar
        BA_Pete
        Super User
        4 years ago

        Hi Anonymous ,

         

        It's possible I'm misunderstanding your meaning a bit here, but you don't need to 'tell' anything to change headers.

        The whole point of having a related calendar table is that you can use fields from the calendar table in visuals, and Power BI's context filtering will automatically select the correct headers based on the values available from the measure. The only control you need to exert is limiting the quantity of values displayed which, in your scenario, the relative month visual filter will achieve.

         

        Pete