Forum Discussion
Cumulative sales
I need the cumulative sales
01 January 2020 - 15 December 2020 (no full month)
01 January 2021 - 15 December 2021
The report is refreshed once a week so both amounts have to increase.
Thanks
Hi jhsimb ,
You can refer the solution in the following links to get the culmulative sales:
Power BI: Calculating Cumulative Totals for Time Periods
Culmulative sales = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) ) )If the above ones can't help you get your expected result, please provide some raw data(exclude sensitive data) in your model tables, visual settings(slicer and matrix visual) and your expected result with more details(screenshot, special examples, calculation logic etc.) Thank you.
Best Regards
2 Replies
- amitchandak
Super User
jhsimb , Is it like YTD based on today max date of data available
YTD QTY forced=
var _max1 = today() //or maxx(allselected('Order'),'order'[Date])
var _max = format(_max,"MMDD")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date', format('Date'[Date],"MMDD")<=_max))
YTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)In comments there are other options which you can try
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 :radacad sqlbi My Video Series Appreciate your Kudos.
Hi jhsimb ,
You can refer the solution in the following links to get the culmulative sales:
Power BI: Calculating Cumulative Totals for Time Periods
Culmulative sales = CALCULATE ( SUM ( 'Table'[Sales] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) ) )If the above ones can't help you get your expected result, please provide some raw data(exclude sensitive data) in your model tables, visual settings(slicer and matrix visual) and your expected result with more details(screenshot, special examples, calculation logic etc.) Thank you.
Best Regards
