Cumulative Sales by Week

[Velocity]

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.

record. I am using following query on attcahed data.

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

[amitchandak]

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/

[Velocity]

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

[amitchandak]

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