Forum Discussion

Mous007's avatar
Mous007
Icon for Helper IV rankHelper IV
5 years ago
Solved

Dynamic measure selection

Hi all,   I am trying to allow my users to compare overdues over the course of the month days.   I could easily achieve the above by creating seperate measures for each month as shown in the scre...
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    Hi, Mous007 , no worries, I come to your rescue, with the LEGEND of line graph ğŸ˜‚

     

    I presume your data is more or less like this,

    The measure can be simplified to

    Total Overdue = SUM( Consolidated[Overdue] )

     

    You might want to refer to the attached file for more details. 

MattAllington's avatar
MattAllington
Icon for Community Champion rankCommunity Champion
5 years ago

You want to write 2 measure. OD this month and OD last month. There are different ways to do this. I personally prefer adding a MonthID column to your calendar, eg 1 for the first month of the calendar, 2 for the second month 13 for the 13th month, 24 for the 24th month, etc. This allows you to easily identify the current and prior month. 


then write

 

TM overdue = CALCULATE(SUM(Consolidated[Overdue]) , filter(all('Dates tables'),Dates tables'[MonthName] = max('Dates tables'[MonthID])))

 

PM overdue = CALCULATE(SUM(Consolidated[Overdue]) , filter(all('Dates tables'),Dates tables'[MonthName] = max('Dates tables'[MonthID])-1))


place slicers on your report to select the current month, and the meausres will update. You can't control the name of the measures, but you can use an additional measure to generate a title for the chart, such as "Overdue for August and Sept"


ps. It is probably easier to understand when written with variables. 
eg

PM overdue = VAR SelectedMonth = max('Dates tables'[MonthID])))

VAR PriorMonth = SelectedMonth - 1

RETURN CALCULATE(SUM(Consolidated[Overdue]) , filter(all('Dates tables'),Dates tables'[MonthName] = PriorMonth))