Forum Discussion

bipowerbix's avatar
bipowerbix
Icon for Helper I rankHelper I
3 years ago
Solved

YTD MEASURE NOT WORKING AS INTENDED

Here is the Table named test:

 

Here is the table 2 which contains Dates, which is used as Slicer:

 

Dates = CALENDAR(date(2022,1,1),TODAY())

 

 

Link between Dates and test dataset:

 

 

Question: Create a Measure that shows the YTD Total amount for the current year, even if the date slicer starts from the previous year:

 

Here is my code:

 

YTD = 
VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date]))
var startyear = DATE(YEAR(TODAY()),1,1)
return if( minim >= startyear , 
calculate(sum(test[Totals] ), FILTER(Dates, Dates[Date] >= startyear))  ,  
 calculate(sum(test[Totals] ), FILTER(Dates, Dates[Date] >= startyear)) )

 

 

Test Scenario 1 Works fine:  Shows correct info, the date slicer starts at 2022, and the measure shows the YTD result of current year

 

Test Scenario 2: Does Not Work, Shows 40 instead of 130 (Expected output 130)

 

Test Scenario 3: Does not work, Even when the year is the current year, (Expected output 130)

 

 

 

 

  •  

    Hi bipowerbix 

     

    I got the desired result, please try the below measure.

    -------------------------------------------------------

     

    YTD =
    VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date])) //12-10-2022
    var startyear = DATE(YEAR(TODAY()),1,1) // current start year date 1-1-2023
    var a= if( minim >= startyear ,
    calculate(sum(test[Totals] ), FILTER(all(Dates), Dates[Date] >= startyear))  ,  
     calculate(sum(test[Totals] ), FILTER(all(Dates), Dates[Date] >= startyear))
     
      )
     return a

    -------------------------------------------------

     

    --------------------------------------------------------------------------

    Regards,

    Novil

    If I answer your question, please mark my post as a solution.

     

     

     

9 Replies

  •  

    Hi bipowerbix 

     

    I got the desired result, please try the below measure.

    -------------------------------------------------------

     

    YTD =
    VAR minim = CALCULATE( MIN( Dates[Date]), ALLSELECTED(Dates[Date])) //12-10-2022
    var startyear = DATE(YEAR(TODAY()),1,1) // current start year date 1-1-2023
    var a= if( minim >= startyear ,
    calculate(sum(test[Totals] ), FILTER(all(Dates), Dates[Date] >= startyear))  ,  
     calculate(sum(test[Totals] ), FILTER(all(Dates), Dates[Date] >= startyear))
     
      )
     return a

    -------------------------------------------------

     

    --------------------------------------------------------------------------

    Regards,

    Novil

    If I answer your question, please mark my post as a solution.

     

     

     

    • bipowerbix's avatar
      bipowerbix
      Icon for Helper I rankHelper I

      Hi FreemanZ,

       

      The expected output for all test scenarios should be 130, that is the YTD total for the current year.

      • FreemanZ's avatar
        FreemanZ
        Icon for Super User rankSuper User

        hi @

        why YTD in scenario 2 and 3 are 130?