How to Dynamically Calculate the Last Eight Fiscal Quarters

[Shelley]

Hello DAX Experts,
I'm working on a dashboard for our leadership. Unfortunately, the sales amount I need to calculate is already a measure in the AAS model, and not the sum of a column, so this makes everything a little more complex. Anyway, I want to show 4 visuals/placeholders for Each fiscal quarter of the current fiscal year. As we complete a quarter, each quarter will be populated with its respective data. So after the fiscal year begins, but before we've completed the first quarter, all these placeholders will be empty.
We are on fiscal year ending September 30.

Each quarter's metric is the sum of the current quarter, plus the previous seven quarters, for a total of eight quarters.

So for 1st quarter, I have this formula, which works, but how can I make the start and end dates dynamic? I thought about using some DAX commands to add up fiscal quarters, but I wasn't able to find anything that indicates how to sum a total of eight fiscal quarters dynamically.

VAR StartDate = DATE ( 2020, 01, 01)
VAR EndDate = DATE (2021, 12, 31)
RETURN
CALCULATE([POS Amt (USD)],
(DATESBETWEEN ('Fiscal Calendar'[Date], StartDate, EndDate))
)

When I try to lookup the fiscal year in the model by using the latest Bill Date as shown below, the result for the measure comes up blank.

_Qtr1 Rolling POS Amt (USD) =

VAR CurrentFY = LOOKUPVALUE('Fiscal Calendar'[Fiscal Year], 'Fiscal Calendar'[Date], MAX('Billings'[Bill Date]))

VAR StartDate = DATE (YEAR(CurrentFY) -2, 01, 01)

VAR EndDate = DATE (YEAR(CurrentFY) - 1, 12, 31)

RETURN

CALCULATE([POS Amt (USD)],

(DATESBETWEEN ('Fiscal Calendar'[Date], StartDate, EndDate))

)

If anyone has any suggestions, I appreciate the help. Thanks.

[v-yalanwu-msft]

Hi， @Shelley ；

Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we could close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,

Community Support Team_ Yalan Wu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @Shelley ;

You could try it.

_Qtr1 Rolling POS Amt (USD) =
VAR StartDate =
    EOMONTH ( ENDOFQUARTER ( 'Billings'[Bill Date] ), -24 ) + 1
VAR EndDate =
    ENDOFQUARTER ( 'Billings'[Bill Date] )
RETURN
    CALCULATE (
        [POS Amt (USD)],
        ( DATESBETWEEN ( 'Fiscal Calendar'[Date], StartDate, EndDate ) )
    )

If this doesn't match your data, can you share a simple file after removing sensitive information and want to output the results?

It makes it easier to give you a solution.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Shelley]

Oh nevermind, I think I figured it out. I had an error in the RETURN portion of my formula.

_Qtr1 Rolling POS Amt (USD) =

VAR CurrentFY = LOOKUPVALUE('Fiscal Calendar'[Fiscal Year], 'Fiscal Calendar'[Date], MAX('Billings'[Bill Date]))

VAR StartDate = DATE (CurrentFY -2, 01, 01)

VAR EndDate = DATE (CurrentFY - 1, 12, 31)

RETURN

CALCULATE([POS Amt (USD)],

(DATESBETWEEN ('Fiscal Calendar'[Date], StartDate, EndDate))

)
If anyone has a better way to do this though, I'm all ears.