Displaying sum of revenue for the same quarter weeks

[Anonymous]

Hi,
I have a an issue I can't figure out.
Here is a sample of my data:

I need to create a measure, which would look at the last closed 4 quarters, and sum up the total revenue for the previous 13 quarter weeks (I show only 2 here).
So on 23 Q1 quarter week 5 I had 1 EUR,
Same quarter week for 23 Q2 I had 2 EUR, so on both rows the total should be 3 EUR as an example.
How do I do it?
Appreciate all the advice!

[govindarajan_d]

Hi @Anonymous ,

Can you try this? Please replace the Table[Revenue] and calendar[date] with the appropriate columns that you are using.

SumOfRevenueLast13Weeks = 
CALCULATE(
    SUM(Table[Revenue]),
    DATESINPERIOD(
        Calendar[Date],
        LASTDATE(Calendar[Date]),
        -13, 
        WEEK
    )
)

[Anonymous]

Thanks for your reply.
Unfortunately typical time intelligence does not work in this file, as it's using custom fical calendar.
Also, the "Week part" does not appear as an option for me, only YEAR, QUARTER, MONTH, DAY

[govindarajan_d]

Can you show me your fiscal calendar?

[Anonymous]

Unfortunately I can't share it. But if you can imagine, for year 2023 fiscal quarter week 1 would start on January 1st
for 2024 fiscal quarter week starts on 31st of December

[govindarajan_d]

Hi @Anonymous,

Can you give me an example of how your calendar table will look like and how you want the result to be? This will help me to better answer your question. 

Are Quarter Label and Quarter Week the only columns it has? We can try window function if those are the only two columns available.