Forum Discussion
Helper IIICumulative 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.
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
- amitchandak
Super User
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/
VelocityThis 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.
