Cumulative Total getting calculated for future months

[Anand_Murthy]

Hello All,

I have a revenue table and a date table , I have created the FY based on my requirements ( April to March).

I'm calculating the monthly cumulative total with the following formula

Cumulative Revenue  =

IF(MIN('Date_Table'[Date]) <= CALCULATE(max('Revenue'[Month]),ALL('Revenue')),

Calculate(Sum('Revenue'[Monthly Revenue]),

Filter(ALL('Date_Table'[Date]),

'Date_Table'[Date] <= max(Date_Table[Date]))))

for FY 2019, I'm getting the right values but for the current year the cumulative number is extending to the future months even though I have included a check in my above formula

I thinking of creating a calulated column which will tell me if a month is past or future and I'm hoping to use this in the cumulative total formula to select on th emonths that are past. I have written the flollowing formula but Its showing past for future months like may, june etc

Future Month =

IF(YEAR('Revenue'[Month]) > year(TODAY())

&& MONTH('Revenue'[Month]) > month(TODAY()),"Future","Past"

any help will be appretiated

[Anonymous]

Here is how to do this properly from the DAX Maestros - A. Ferrari and M. Russo:

https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/

Best
D

[amitchandak]

@Anand_Murthy ,

Try like

Calculate(Sum('Revenue'[Monthly Revenue]),
Filter(ALL('Date_Table'[Date]),
'Date_Table'[Date] <= max(Date_Table[Date])), 'Date_Table'[Date]<today())

If you only need yearly cumulative use YTD

examples

YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date])
LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))

YTD QTY forced= 
var _max = today()
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[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
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)

YTD QTY forced= 
var _max = maxx('order',[Order date])
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max1 =maxx('order',[Order date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
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)