cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Cumulative Sales by Week

Hi,

I am unable to get Cumulative sales correctly when there is more than one date

record. I am using following query on attached data.

Cumulative Sales =
CALCULATE(
    SUM(Table1[Sales]),
    FILTER(
        ALLEXCEPTTable1,Table1[Date]),
        Table1[WeekNum] <= MAX(Tabl1[WeekNum])
    )
)

The issue is that it considering only that date which has got data in it. For the weeks, it doesn't have data, it doesn't add previous weeks' sales figures. Any help will be appreciated. Thanks.

Untitled.png

 

record. I am using following query on attcahed data.

Cumulative Sales =
CALCULATE(
    SUM(Table1[Sales]),
    FILTER(
        ALLEXCEPTTable1,Table1[Date]),
        Table1[WeekNum] <= MAX(Tabl1[WeekNum])
    )
)

3 REPLIES 3
Highlighted
Super User IV
Super User IV

Re: Cumulative Sales by Week

Move week to a different table or use a date table. Your table has week as  a date to even Date table should work And try like

Cumulative Sales =
CALCULATE(
SUM(Table1[Sales]),
FILTER(
ALL(Date[Date]),
date[WeekNum] <= MAX(Date[WeekNum])
)
)

or
Cumulative Sales =
CALCULATE(
SUM(Table1[Sales]),
FILTER(
ALLselected(Date[Date]),
date[WeekNum] <= MAX(Date[WeekNum])
)
)

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper II
Helper II

Re: Cumulative Sales by Week

This doesn't solve my problem as WeekNum in which sales is happening is going to be different for each row(date). I need to find the average sales happening in each week based on number of dates participating (user selection).

Highlighted
Super User IV
Super User IV

Re: Cumulative Sales by Week

@Velocity, Can you share sample data and sample output.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors