Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi guys,
I'm fairly new to Power BI and my DAX knowledge is fairly limited, so bear with me.
I have a tabel with the following information:
Month Start, Company, # Safety Briefings for month
1/01/2016, Company A, 5
1/01/2016, Company B, 10
1/01/2016, Company C, 15
1/02/2016, Company A, 6
1/02/2016, Company B, 4
1/02/2016, Company C, 6
etc
What I want to do is calculate the total number of safety briefings completed over the past 3 months.
I have tried creating a new measure using the following:
Safety Briefings last 3 months = CALCULATE(sum(Table1[Safety Briefings]), DATESINPERIOD(Table1[Start Date], LASTDATE(Table1[Start Date]), -3, MONTH))
However, this only gives me the total for the month, NOT the past 3 months.
Any help would be greatly appreciated.
Cheers
Solved! Go to Solution.
Hi @CourtneyLeah, it would be best to have a Date Table and link your Month Start to the date table.
If that is done you could then create the following calculated measure which should work for you.
Safety Briefings last 3 months = CALCULATE ( SUM ( Table1[Safety Briefings] ), DATEADD ( 'Date'[Calendar Date], -3, MONTH ) )
How do you do the calculate sum of the past 3 months for each company?
I created a measure using your dax below, but it doesn't work if I put in a filter for each company
Cheers
Hi @CourtneyLeah, it would be best to have a Date Table and link your Month Start to the date table.
If that is done you could then create the following calculated measure which should work for you.
Safety Briefings last 3 months = CALCULATE ( SUM ( Table1[Safety Briefings] ), DATEADD ( 'Date'[Calendar Date], -3, MONTH ) )
I am a little confused by this solution. From what I understand this won't summarize over all three months, it will just summarize what happened three months ago. I.e. if I put "Safety Briefings last 3 months" in a table grouped per month and look at the April-row, then I will see the sum of January and not the sum of Januari-March, right!?
Hmmm... do you mean like this? To me this doesn't look like the sum of the last three months but I might misunderstand you?
For anyone else stumbling into this thread these solutions might be of interest (2 more ways of doing almost the same thing are commented out). They could all be useful depending on challenge...
Great formula! Thanks!
In case you want to show the total of jan + Feb (ie 210) in Feb line instead of Mar line, then this works:
DATESINPERIOD('Dim Calendar'[Date], LASTDATE(DATEADD('Dim Calendar'[Date], 0, MONTH)), -3, MONTH)
That is great! So simple example with the datesbetween function!
Thank you very much!
Hi @CourtneyLeah,
Please mark the corresponding reply as answer, which will help more people find solution clearly.
Best Regards,
Angelia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
82 | |
70 | |
64 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |