Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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 =
Solved! Go to 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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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
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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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-...
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?
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))
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |