Cumulative sales

[jhsimb]

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

[v-yiruan-msft · Accepted solution]

Hi @jhsimb ,

You can refer the solution in the following links to get the culmulative sales:

Cumulative Total

Cumulative total

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

[amitchandak]

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