Forum Discussion

admin_xlsior's avatar
admin_xlsior
Icon for Post Prodigy rankPost 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], ...
  • 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!

ValtteriN's avatar
ValtteriN
Icon for Community Champion rankCommunity 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!