cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
morz3d Regular Visitor
Regular Visitor

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]),
                                'Date'[Date]<=LASTNONBLANK('Data'[Date],[Amount Sum])
                                ),
               -1,Year
               )
       )
 

 

But problem i am having is that calculation starts only after first record in curret year. It follwing picture we can see that calcualtion starts from January 11th instead of January 1st. Is there a way to fix this? 

YTD PY.PNG

 

 

Download PBIX

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Previous Year YTD, DAX help

@morz3d 

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
    )

 

 

 

8 REPLIES 8
Nick_M Senior Member
Senior Member

Re: Previous Year YTD, DAX help

YTD Prev Year = CALCULATE( [YTD], SAMEPERIODLASTYEAR('Date'[Date]))

Prev YTD Fixed.png

morz3d Regular Visitor
Regular Visitor

Re: Previous Year YTD, DAX help

Hi @Nick_M 

Thanks for quick response i already tried something similar but problem is that like that i will have entire previous year shown,

and i want only to se PY YTD till last nonblank value of current year with this

 'Date'[Date]<=LASTNONBLANK('Data'[Date],[Amount Sum])
morz3d Regular Visitor
Regular Visitor

Re: Previous Year YTD, DAX help

I have also added few more months in date table. PBIX is available on same link

Highlighted
Super User
Super User

Re: Previous Year YTD, DAX help

Hi @morz3d 

 

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] ) )
    )
morz3d Regular Visitor
Regular Visitor

Re: Previous Year YTD, DAX help

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.

 

 

YTD PY 2.PNG

Super User
Super User

Re: Previous Year YTD, DAX help

@morz3d 

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
    )

 

 

 

morz3d Regular Visitor
Regular Visitor

Re: Previous Year YTD, DAX help

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 

morz3d Regular Visitor
Regular Visitor

Re: Previous Year YTD, DAX help

Thanks @AlB, you are a life saver Smiley Happy