Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion

    Anonymous 

     

    Sample table

     

    DateSales
    01-01-2018200
    01-02-2018210
    01-03-2018220
    01-04-2018230
    01-05-2018240
    01-06-2018225
    01-07-2018210
    01-08-2018195
    01-09-2018180
    01-10-2018200
    01-11-2018220
    01-12-2018240
    01-01-2019260
    01-02-2019280
    01-03-2019300
    01-04-2019290
    01-05-2019280
    01-06-2019270
    01-07-2019260
    01-08-2019300
    01-09-2019340
    01-10-2019380
    01-11-2019420
    01-12-2019460
    01-01-2020500
    01-02-2020480
    01-03-2020460
    01-04-2020440
    01-05-2020420

     

    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
    🙂

  • VasTg's avatar
    VasTg
    Memorable 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