Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Previous Year YTD, DAX help

Hi guys,  I am having problem with YTD for previous year. I am using following formula so i can compare YTD till current date YTD LY = CALCULATE([YTD],DATEADD( FILTER(DATESYTD('Date'[Date]), ...
Need Help
Show and Tell
Tips and Tricks
  • AlB's avatar
    AlB
    7 years ago

    Anonymous 

    Edited

     

    YTD LY V2_B =
VAR _Last =
    CALCULATE ( LASTNONBLANK ( Data[Date]; [YTD] ); ALL ( 'Date'[Date] ) )
VAR _LastVal =
    CALCULATE (
        CALCULATE ( [YTD]; SAMEPERIODLASTYEAR ( 'Date'[Date] ) );
        'Date'[Date] = _Last
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Date'[Date] ) <= _Last;
        CALCULATE ( [YTD]; SAMEPERIODLASTYEAR ( 'Date'[Date] ) );
        _LastVal
    )

     

     

     

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
7 years ago

Hi Anonymous 

 

This might be a quick and dirty solution:

 

YTD LY V2 =
VAR _Last =
    CALCULATE ( LASTNONBLANK ( Data[Date]; [YTD] ); ALL ( 'Date'[Date] ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Date'[Date] ) <= _Last;
        CALCULATE ( [YTD]; SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
    )

or this if you want to show the result only where [Amount PY] is non-blank 

 

YTD LY V3 =
VAR _Last =
    CALCULATE ( LASTNONBLANK ( Data[Date]; [YTD] ); ALL ( 'Date'[Date] ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Date'[Date] ) <= _Last
            && NOT ISBLANK ( [Amount PY] );
        CALCULATE ( [YTD]; SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
    )
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Thanks AlB 

I fount this one lamost perfectly fitting my needs.

Is there maybe a way to fill other cell values with last YTD value (44 in thi case), like we have for normal YTD function.

So basically YTD PY would summ all PY values will last value of current day, and after that one it will just show lates value.

 

 

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Anonymous 

    Edited

     

    YTD LY V2_B =
VAR _Last =
    CALCULATE ( LASTNONBLANK ( Data[Date]; [YTD] ); ALL ( 'Date'[Date] ) )
VAR _LastVal =
    CALCULATE (
        CALCULATE ( [YTD]; SAMEPERIODLASTYEAR ( 'Date'[Date] ) );
        'Date'[Date] = _Last
    )
RETURN
    IF (
        SELECTEDVALUE ( 'Date'[Date] ) <= _Last;
        CALCULATE ( [YTD]; SAMEPERIODLASTYEAR ( 'Date'[Date] ) );
        _LastVal
    )

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      This woul add more data from PY to the YTD sum.

      Considering this case i would like to remain on sum of 44 since this is all PY data available till January 13th 

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks AlB, you are a life saver :smileyhappy: