Dynamic measure selection

[Mous007]

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 screen shot below:

The issue with my approach is that i will have to create a specific measure for each month and hence not efficient.

I would like to dynamically be able to get the above results based on a slicer selection with the different months available ( e.g. user only select August and september on the slicer and the line graph populates automatically)

My measure is fairly simple = 

August overdue = CALCULATE(SUM(Consolidated[Overdue]) , 'Dates tables'[MonthName] = "August")

It would be great if  anyone can help me get my monthly measure dynamic using the slicer selection or guide me on how i should/can do it.

Thank you in advance

[Mous007]

Thank you all for your replies.

@CNENFRNL your solution is actually what i am looking for for a first draft and it does the job indeed. I have no idea why i haven/t taught about it as it is fairly simple.

Thank you all again for the support

[CNENFRNL · Accepted solution]

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. 

[Mous007]

Hi @MattAllington , thank you for the prompt reply.

I am actually not trying to get the current month compared to previous month. But i would like to users to choose any month from the slicer (Jan to Dec) e.g. select July. Sep and Nov and show the corresponding values into the graph.

Apologies if my request isnt clear enough.

[amitchandak]

@Mous007 , In such a case you have two ways. Keep a snapshot of every month. Or rebuild based on logic when measure is called(Slicer values /date can help)

Dax append can help

https://blog.crossjoin.co.uk/2020/04/13/keep-the-existing-data-in-your-power-bi-dataset-and-add-new-...
https://www.thebiccountant.com/2017/01/11/incremental-load-in-powerbi-using-dax-union/

Or you need to provide some logic to calculate. Can you share sample data and sample output in table format?

[MattAllington]

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