DATESYTD and Drilldown

[zctqslo]

Hello,

I'm trying to calculate a running sum that resets at the end of each year (31st December) and correctly reflects when drilling down through quarters and months. Let's say I've Table1 with two columns:

- Date

- Revenue

So far I've tried:

TRIAL1 = CALCULATE(SUM(Table1[Revenue]), FILTER(ALL(Table1), ISONORAFTER(Table1[Date], MAX(Table1[Date]), DESC)))

This reflects the drilldown accurately, but doesn't reset from 0 on Jan 1 of a new year. Instead, it keeps adding.

Therefore I've tried the following, which correctly resets the sum each year but when, drilling down, it shows the value for the specific quarter (or month, or day) without aggregating accurately.

TRIAL2 = CALCULATE(SUM(Table1[Revenue]), DATESYTD(Table[Date]))

I've tried to combine the two but without much success, suggestions? Thank you! 

[V-lianl-msft · Accepted solution]

Hi @zctqslo ,

Try to create measure like this:

RunningTotal = CALCULATE(
SUM( table1[revenue] ),
FILTER(  ALL(Table1) ,
SUMX( FILTER( Table1, EARLIER( Table1[date] ) <= Table1[date] &&YEAR(table1[date])=YEAR(EARLIER(table1[date]))), table1[revenue] )
)
)

Sample .pbix

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@zctqslo , You can have the following for YTD. But you need to use Calendar

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)

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

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/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Greg_Deckler]

See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...