Forum Discussion

admin_xlsior's avatar
admin_xlsior
Post Prodigy
4 years ago
Solved

DATESYTD not working ?

Hi guys,

 

Can DATESYTD working without any context ?

I have this data like :

Is having this kind of measure :

 

All net sales YTD = CALCULATE(
                        [All net sales],
                        DATESYTD(Dates[Date])
)

 

works ?

 

If based on my table, shouldn't I receive a total of net sales for year of 2021 ? April - Sept 2021 ? Because currently it is blank.

If I click on the row of that table, for example April 1 2021, then the measure is correct, returned the exact value (87,800), but when none selected which is what I want because I only need the total of sales "up-to-this-year" which mean I do not need that table, then it returned blank.

 

Thanks

 

  • ValtteriN's avatar
    ValtteriN
    4 years ago

    Hi,

    If I understood your problem correctly you would like to have YTD measure in a KPI card or something similar to that? Based on the problem description I think your slicer selection is not working as you would hope and thus you get the blank value.  Instead of Datesytd you could use the following approach:

    My test data:

    Ideal outcome:
    So in this example the goal is that without any slicers the YTD measure would return 350. That is this year's running total until today. If something is selected the calculation should be done until the selected day. So for example If I select 4.12.2021 the calculation would be from this time period: 1.1.2021-4.12.2021.

    Measure using datesbetween ([Base measure] is just SUM of [Value]):

    YTD from base =
    var test = HASONEFILTER(YTD_Example[Date])
    var _sdate = if(test,DATE(YEAR(SELECTEDVALUE(YTD_Example[Date])),1,1), DATE(YEAR(TODAY()),1,1))
    var _edate = if(test,MAX(YTD_Example[Date]),TODAY()) return
    CALCULATE([Base measure],DATESBETWEEN(YTD_Example[Date],_sdate,_edate))

     

    End result:
    Without selection (calculates until today):

    With date selected (calculates until selection:

    Hopefully this can be used to resolve your issue and if it does accept this as a solution!

4 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

    If I understood your problem correctly you would like to have YTD measure in a KPI card or something similar to that? Based on the problem description I think your slicer selection is not working as you would hope and thus you get the blank value.  Instead of Datesytd you could use the following approach:

    My test data:

    Ideal outcome:
    So in this example the goal is that without any slicers the YTD measure would return 350. That is this year's running total until today. If something is selected the calculation should be done until the selected day. So for example If I select 4.12.2021 the calculation would be from this time period: 1.1.2021-4.12.2021.

    Measure using datesbetween ([Base measure] is just SUM of [Value]):

    YTD from base =
    var test = HASONEFILTER(YTD_Example[Date])
    var _sdate = if(test,DATE(YEAR(SELECTEDVALUE(YTD_Example[Date])),1,1), DATE(YEAR(TODAY()),1,1))
    var _edate = if(test,MAX(YTD_Example[Date]),TODAY()) return
    CALCULATE([Base measure],DATESBETWEEN(YTD_Example[Date],_sdate,_edate))

     

    End result:
    Without selection (calculates until today):

    With date selected (calculates until selection:

    Hopefully this can be used to resolve your issue and if it does accept this as a solution!

  • Mikelytics's avatar
    Mikelytics
    Resident Rockstar
    4 years ago

    HI admin_xlsior ,

     

    For DatesYTD you need date filter context.

     

    Measure

     

     

    without fitler context

     

    with filter context

    other filter context (here 2020 is ignored since I also chose something from 2021)

     

    ________________________

    If this post helps, then please Accept it as the solution to help other community members find it more quickly

    Click on the Thumbs-Up icon if you like this reply.

     

     

    • admin_xlsior's avatar
      admin_xlsior
      Post Prodigy
      4 years ago

      Hi both,

       

      Thanks, I thought it does not need context for this particular function, but it does. Initially I do not intent to have any slicer for this time. 

       

      ValtteriN , this is so cool. 

       

      Thanks