How to modify LYTD Qty expression , So that it will display Last Year To Date Qty ?

[admin11]

Hi All

@Vera_33  share with me below expression working fine , in order to return Qty for Jan - Mar 2021 , user need to manually tick on year=2021 , month =3 :-

CNT_LYTD_COY = TOTALYTD(Distinctcount(SALES[Customer Name]),DATEADD('Date'[Date], -1, YEAR ), ALL( 'Date' ) )

Can pls help me modify the above expression , so that when i filter year = 2021 and month = march ( user don't need tick year=2021 , month = 3) , it will return count qty from 2021 Jan till March.

Paul

[amitchandak · Accepted solution]

@admin11 ,

Try like (small change in YTD and LYTD)

YTD=
var _min = date(year(today()),1,1)
var _day = today()
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year] =year(Today()) && 'Date'[Day of Year] <= _day) )

LYTD =
var _min = date(year(today())-1,1,1)
var _max = date(year(today())-1,month(today()),day(today()))
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year] = year(Today())-1 && 'Date'[Day of Year] <= _max))

[Vera_33]

Hi Paul,

I actually don't quite understand your question, why you filter the date and the user needs to filter again? Did @amitchandak answer your question?

[admin11 · Accepted solution]

@Vera_33 @amitchandak 

Below is the 2 expression work fine , user don't need to filter year & month :-

CNT CUST ID LYTD_FINAL =

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

return

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

CNT CUST ID YTD_FINAL =

var _max = today()

return

calculate(TOTALYTD(DISTINCTCOUNT('SALES'[CUST_ID]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

Thank you both of you helping.

Paul

[admin11]

@Vera_33 

your exxpression below is working fine :-

CNT_LYTD_COY = TOTALYTD(Distinctcount(SALES[Customer Name]),DATEADD('Date'[Date], -1, YEAR ), ALL( 'Date' ) )

But the user need to slect year=2021 , month=3 in order to get LYTD total. If user does not select year=2021 , month =3 , it will return whole year value. 

As you aware that YTD have no issue. 

At the end i need to compare the growth rate account. 

Hope you understand.

@amitchandak  Solution is correct , it is what i need , it will return LYTD from Jan - march 2021. But due to small error , it display null , now i waiting for him to help me correction.

Paul Yeo 

[amitchandak]

@admin11 , YTD and LYTD , without use selecting a date

YTD=
var _min = date(year(today()),1,1)
var _day = datediff(_min, today(),day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year] =year(Today()) && 'Date'[Day of Year] <= _day) )

LYTD =
var _min = date(year(today())-1,1,1)
var _max = date(year(today())-1,month(today()),day(today()))
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year] = year(Today())-1 && 'Date'[Day of Year] <= _day))

[admin11]

@amitchandak 

Thank you so much for your sharing. i try to use your expression i get null display.

COUNT YTD AMIT =

var _min = date(year(today()),1,1)

var _day = datediff(_min, today(),day)+1

return

CALCULATE(Distinctcount('SALES'[CUST_ID]), FILTER(ALL('Date'),'Date'[Year] =year(Today()) && 'Date'[Date] <= _day) )

My PBI file :-

https://www.dropbox.com/s/hh9isfpxb99m5t9/PBT_V2021_353%20LYTD%20COUNT.pbix?dl=0

Paul

[amitchandak · Accepted solution]

@admin11 ,

Try like (small change in YTD and LYTD)

YTD=
var _min = date(year(today()),1,1)
var _day = today()
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year] =year(Today()) && 'Date'[Day of Year] <= _day) )

LYTD =
var _min = date(year(today())-1,1,1)
var _max = date(year(today())-1,month(today()),day(today()))
var _day = datediff(_min, _max,day)+1
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year] = year(Today())-1 && 'Date'[Day of Year] <= _max))

[admin11]

@amitchandak 

Thank you very much , it work fine now.

Paul