Calculate sum of the past 3 months

[CourtneyLeah]

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

[pandakaka]

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

[GilbertQ]

Hi @pandakaka

What you could do is to keep the measure and then put each company onto the Rows?

[GilbertQ · Accepted 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 )
)

Formatted using DAX Formatter

[Anonymous]

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!?

[GilbertQ]

Hi there, the proposed solution will go back 3 months and then SUM all the data starting 3 months prior.

So if you had to put this measure into a table and drag in the Month column from your date table you should then see the previous 3 months

[Anonymous]

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?

[Anonymous]

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

[daniel_baciu]

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)

[Anonymous]

That is great! So simple example with the datesbetween function!

Thank you very much!

[CourtneyLeah]

Thanks @GilbertQ. The date table has helped enormously.

Cheers

[v-huizhn-msft]

Hi @CourtneyLeah,

Please mark the corresponding reply as answer, which will help more people find solution clearly.

Best Regards,
Angelia

[GilbertQ]

Hi @CourtneyLeah, glad that it helped out