Today my LYTD expression not working after i change the master calendar date range

[Paulyeo11]

Hi All

When i set my master calendar :-

Date = CALENDAR(Date(2000,01,01),date(2020,12,31))

Below expression work fine :-

Sales LYTD =

var _max = date(year(today())-1,month(today()),day(today()))

return

TOTALYTD(('SALES'[SALES_]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

I add sales target amount for 2021. So i need to adjust the 

Date = CALENDAR(Date(2000,01,01),date(2022,12,31))

Below expression not work  :-

Sales LYTD =

var _max = date(year(today())-1,month(today()),day(today()))

return

TOTALYTD(('SALES'[SALES_]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

Can some one share with me how to make the above expression work again ?

Paul

[amitchandak]

@Paulyeo11 , if there is no date filter on page and you are not using any date related stuff on visual then you will not get data. As 31-dec-2022 is your date when there is no date in the context for totalytd

You can also try this option, but date should be in context

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)

[Paulyeo11]

Hi Amit

Thank you for sharing.

If i set the master calendar date below :-

Date = CALENDAR(Date(2000,01,01),date(2020,12,31))

My next year target expression not working , it display Null:-

Target_2021 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],-1,YEAR)))

If i change the master cal date to below :- 

Date = CALENDAR(Date(2000,01,01),date(2021,12,31))

Below expression working fine :-

Target_2021 = CALCULATE([TARGET_], DATESYTD(DATEADD('Date'[Date],-1,YEAR)))

But it affect LTYD expression :-

Sales LYTD =

var _max = date(year(today())-1,month(today()),day(today()))

return

TOTALYTD(('SALES'[SALES_]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

Paul Yeo

[v-shex-msft]

HI @Paulyeo11,

Can you please share some dummy data to test? It is hard to troubleshoot and test with your formulas without any detailed data.

How to Get Your Question Answered Quickly 

Regards,
Xiaoxin Sheng

[Paulyeo11 · Accepted solution]

Hi Shex 

I already solve the issue. the master calendar end year 2020 , should not be make any changes. it was due to i need to set 2021 sales target amount , and instead of force my sales target 2021 to display 2021 value , i don't know i go and change the master calendar end year from 2020 to 2021 , the side effect is it will affect LYTD sales expression display Null. So far i have encounter few major issue suddenly not working for example cannot refresh data using share point due to need to add serurity access , also setting section access control. and many other items also. never ending .

Paul Yeo

Paul

[v-shex-msft]

There'@Paulyeo11,

I'm glad to hear you solve this problem. Looks like you've still faced some extra problems. For this scenario, I would like to suggest that you share more detailed information here. Then we can take a look at these problems and try to fix them.

Best regards

Xiaoxin Sheng

[amitchandak]

@Paulyeo11 , Please add a date or year filter to the page and check that. If you do not add a filter/slicer which date will be used for YTD and LYTD is very important.

Date from the row context(year, month or date will give that) , if not the last date of the calendar

[AlB]

Hi @Paulyeo11 

How/where are you using the mesure exactly? 

What is it that does not work exactly?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers