Calculating Rolling 4 Quarters by fiscal year

[aharris15]

Hello,

I tried searching around trying to figure out how to calculate a rolling 4 quarter total of units, being based on the fiscal year. I want the rolling 4 quarters to include the current quarter and then the previous 3 quarters. I have a Calendar dimension table that I added in columns to determine my fiscal year and quarter. Below is the current measure that I'm using that I found in a tutorial - while it has no errors, it also doesn't give me any results. 

Rolling 4 Qtr Unit Total =

var StartDay = NEXTDAY(LASTDATE(DATEADD('Calendar'[Date].[Date],-4,QUARTER)))

var EndDay = LASTDATE('Calendar'[Date].[Date])

return

CALCULATE([Total Units],DATESBETWEEN('Calendar'[Date].[Date],StartDay,EndDay))

Any help is appreciated!

[parry2k]

@aharris15 good to hear it is working, although it looks like a small/simple step it is super CRITICAL

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[parry2k · Accepted solution]

@aharris15 it is good you have a calendar dimension but you need to mark it as a date table, check my videos on times intelligence and then your measure will work, assuming start day and end day are getting calculated correctly. Learn basics of Time Intelligence DAX functions - Part 1 - YouTube

Rolling 4 Qtr Unit Total =
var StartDay = NEXTDAY(LASTDATE(DATEADD('Calendar'[Date],-4,QUARTER)))
var EndDay = LASTDATE('Calendar'[Date])
return
CALCULATE([Total Units],DATESBETWEEN('Calendar'[Date],StartDay,EndDay))
 

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.

[aharris15]

Oh wow, that seemed to work! Thank you - can't believe I missed that little step!