YTD calculation

[Anonymous]

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!

[VasTg]

@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

[nandukrishnavs]

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

Output

Did I answer your question? Mark my post as a solution!
Appreciate with a kudos 🙂