Forum Discussion
Anonymous
6 years agoNot applicable
YTD calculation
Hi all,
I have written a DAX to calculate YTD Net Sales till end of Last Month, see below:
YTD 2018_ = CALCULATE(SUM('Sales'[Net Sls Sd]),
'My Calendar'[Date]>=DATE(YEAR(TODAY())-2,MONTH(1),DAY(1)),
'My Calendar'[Date]<DATE(YEAR(TODAY())-2,MONTH(TODAY()),DAY(1)))
I am not getting any results, can someone tell me what is wrong with this DAX query?
Thanks in advance!
2 Replies
- nandukrishnavsCommunity Champion
Anonymous
Sample table
Date Sales 01-01-2018 200 01-02-2018 210 01-03-2018 220 01-04-2018 230 01-05-2018 240 01-06-2018 225 01-07-2018 210 01-08-2018 195 01-09-2018 180 01-10-2018 200 01-11-2018 220 01-12-2018 240 01-01-2019 260 01-02-2019 280 01-03-2019 300 01-04-2019 290 01-05-2019 280 01-06-2019 270 01-07-2019 260 01-08-2019 300 01-09-2019 340 01-10-2019 380 01-11-2019 420 01-12-2019 460 01-01-2020 500 01-02-2020 480 01-03-2020 460 01-04-2020 440 01-05-2020 420 DAX measure
Measure YTD = CALCULATE(SUM('Table'[Sales]),DATESYTD('Table'[Date]))YTD 2018 = var startDate= DATE(2018,1,1) var lastMonthENDdate= EOMONTH(TODAY(),-1) var endDate= SELECTEDVALUE('Table'[Date],lastMonthENDdate) var sumVal= CALCULATE(SUM('Table'[Sales]),ALL('Table'[Date]),'Table'[Date]<= endDate&&'Table'[Date]>=startDate) return sumValOutput
Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂 - VasTgMemorable Member
Anonymous
Looks like you are trying to calculate the sales for 2018 from 1/1 till previous month-end.
I would recommend something like below.
YTD 2018_ = VAR T = TODAY () VAR _START = DATE ( YEAR ( T ) - 2, 01, 01 ) VAR _END = DATE ( YEAR ( T ) - 2, MONTH ( T ) - 1, DAY ( T - DAY ( T ) ) ) RETURN CALCULATE ( SUM ( 'Sales'[Net Sls Sd] ), FILTER ( ALL ( 'My Calendar' ), AND ( 'My Calendar'[Date] >= _START, 'My Calendar'[Date] <= _END ) ) )I haven't tested it but hope you get the idea.
If it helps, mark it as a solution
Kudos are nice too