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

[Anonymous]

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

[amitchandak · Accepted solution]

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

[Anonymous]

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)

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