Measure to get sum of last 3 months but excluding last 3 months

[nh27]

Hi All,

Looking for some help on this one - I need a measure that gives me the total qty but for the last 3 months but this should start from 3 months back.



The above measure does not seem to be pulling in the correct totals.

Example: I have selected April 2024 in my slicer, I want the sum of qty for November 2023, December 2023 & January 2023.

Where am I going wrong?

TIA

[NaveenGandhi · Accepted solution]

Hi @nh27 

Try below measure. I have adjusted the start and end date to capture month ends as you except. Right now the problem is the startdate for example will capture from october 31st if you select april[edate moves exactly to 30th day of any given month i.e startdate = oct 30], Same issue with Enddate[will pull till 30th of the given month rather than 31st if that month has till 31st]

Prev 6_3=

VAR currentmonth = max(DimDate[Datekey])

VAR StartDate = CALCULATE(ENDOFMONTH(DimDate[Datekey]),DimDate[Datekey]=EDATE(currentmonth,-6))

VAR EndDate = CALCULATE(ENDOFMONTH(DimDate[Datekey]),DimDate[Datekey]=EDATE(currentmonth,-3))

RETURN

CALCULATE(FactSales[SalesAmount#],DimDate[Datekey]>StartDate&&DimDate[Datekey]<=EndDate)

Let me know if this helps.

 If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on Linkedin!!!

[nh27]

Hi @NaveenGandhi thank you very much this has done the trick!