Forum Discussion
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 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 =
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.
5 Replies
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.
egPM overdue = VAR SelectedMonth = max('Dates tables'[MonthID])))
VAR PriorMonth = SelectedMonth - 1
RETURN CALCULATE(SUM(Consolidated[Overdue]) , filter(all('Dates tables'),Dates tables'[MonthName] = PriorMonth))
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.
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.
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-data-to-it-using-incremental-refresh/
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?
