topic Re: Displaying sum of revenue for the same quarter weeks in DAX Commands and Tips

Displaying sum of revenue for the same quarter weeks

Anonymous — Fri, 02 Feb 2024 10:58:47 GMT

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!

Re: Displaying sum of revenue for the same quarter weeks

govindarajan_d — Fri, 02 Feb 2024 11:15:57 GMT

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

Re: Displaying sum of revenue for the same quarter weeks

Anonymous — Fri, 02 Feb 2024 11:23:58 GMT

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

Re: Displaying sum of revenue for the same quarter weeks

govindarajan_d — Fri, 02 Feb 2024 11:49:18 GMT

Can you show me your fiscal calendar?

Re: Displaying sum of revenue for the same quarter weeks

Anonymous — Fri, 02 Feb 2024 12:29:24 GMT

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

Re: Displaying sum of revenue for the same quarter weeks

govindarajan_d — Fri, 02 Feb 2024 14:01:22 GMT

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.