topic Re: DAX measure - Stop the Line chart at current week for YTD Sales in DAX Commands and Tips

DAX measure - Stop the Line chart at current week for YTD Sales

Anonymous — Thu, 08 Apr 2021 08:44:15 GMT

Hello All

You can see all last three YTD 2018, 2019, 2020 are plotted on the single graph to compare against 2021 YTD. The desired output that i like to achieve is to stop at current week the line graph.

Attached PBIX and used DAX syntax

https://drive.google.com/file/d/1KfQfWMP3eKebZoF7R2Xmz6Cce4RXsSKD/view?usp=sharing

1. SCE_Sales $ = SUMX(SCE,SCE[Sales $])

2. SCE YTD $ = TOTALYTD([SCE_Sales $],'Date'[Date])

3. View (All Year YTD) =

VAR YearsBack = year(today()) - SELECTEDVALUE('Year'[Year])

RETURN

CALCULATE([Electric YTD kWh],

DATEADD('Date'[Date], -YearsBack, YEAR)

)

Presently the current year YTD sales are only populated up to week 15 because weeks 16 – 52 have not happened yet (when the report was first build). From Week 16 2021 sales are showing flat-lining till week 52 which is misleading.

How can I tweak the current DAX to stop (RED CIRCLED) from week 15 the 2021 YTD Black line?

Would be very grateful if you can help me with modifying my current DAX or new DAX suggestion to stop at week 15 the 2021 YTD Blank line?

Appreciate your help in advance.

Many Thanks

Re: DAX measure - Stop the Line chart at current week for YTD Sales

amitchandak — Thu, 08 Apr 2021 09:22:22 GMT

Anonymous , Try to force YTD to stop

Examples

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)

Re: DAX measure - Stop the Line chart at current week for YTD Sales

Anonymous — Thu, 08 Apr 2021 16:46:41 GMT

Thanks amitchandak - Your DAX solution worked magically, Appreciate your help.

I used this one to get the solution for future reference.

--------------------------------------------------------------------------------------------------------

YTD QTY forced 2 =

var _max = today()

return

if(max('Date'[Date])<=_max, calculate(Sum(SCE[Sales $]),DATESYTD('Date'[Date])), blank())

--------------------------------------------------------------------------------------------------------

View (All Year YTD) 2 =

VAR YearsBack = year(today()) - SELECTEDVALUE('Year'[Year])

RETURN

CALCULATE([YTD QTY forced 2],

DATEADD('Date'[Date], -YearsBack, YEAR)

--------------------------------------------------------------------------------------------------------