How to add one more condition to my YTD expression

[admin11]

Hi All

Sales YTD =

Below expression working fine :-

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

return

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

May i know how to add below condition to above expression :-

filter(SALES,[SOURCE] = "TS")

I get above error msg when i try :-

Sales YTD TDS =

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

return

TOTALYTD((filter(SALES,[SOURCE] = "TS"),'SALES'[SALES_]),dateadd('Date'[Date],-0,year),'Date'[Date]<=_max)

Below is my PBI file :-

https://www.dropbox.com/s/xr3eegijic9zbg5/PBT_V2021_382%20TI_SI_GL%20SAMPLE%20ytg%20add%20condition%...
Paul

[themistoklis · Accepted solution]

Hello @admin11 ,

Please try the following formula:

Sales YTD TDS = 
var _max = date(year(today())-0,month(today()),day(today()))
return
Calculate(TOTALYTD(('SALES'[SALES_]),dateadd('Date'[Date],-0,year),'Date'[Date]<=_max),filter(SALES,[SOURCE] = "TS"))

[PhilipTreacy · Accepted solution]

@admin11 

Hi Paul,

Your measures can be simplified, e.g. for Sales YTD

Sales YTD = TOTALYTD(('SALES'[SALES_]),'Date'[Date],'Date'[Date]<=TODAY())

and for this particular question, Sales YTD TS

Sales YTD TS = CALCULATE(TOTALYTD(('SALES'[SALES_]),'Date'[Date],'Date'[Date]<=TODAY()), FILTER('SALES', 'SALES'[SOURCE] = "TS"))

Download your PBIX

Regards

Phil

[admin11]

@PhilipTreacy 

Thank you for sharing the shorter expression.

For me since i no good in writng script , so i prefer YTD & LYTD as below :-

Sales YTD =

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

return

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

Sales LYTD =

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

return

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

Which more easy for me to manage.

Paul

[themistoklis · Accepted solution]

Hello @admin11 ,

Please try the following formula:

Sales YTD TDS = 
var _max = date(year(today())-0,month(today()),day(today()))
return
Calculate(TOTALYTD(('SALES'[SALES_]),dateadd('Date'[Date],-0,year),'Date'[Date]<=_max),filter(SALES,[SOURCE] = "TS"))