Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Runicas
Frequent Visitor

Displaying sum of revenue for the same quarter weeks

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

Runicas_0-1706871292367.png

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!

5 REPLIES 5
govindarajan_d
Solution Supplier
Solution Supplier

Hi @Runicas ,

 

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


 

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

 

Can you show me your fiscal calendar?

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

Hi @Runicas,

 

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. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.