Forum Discussion

FR14's avatar
FR14
Icon for Helper I rankHelper I
2 years ago
Solved

LY YTD (total)

Hi Everyone,   Using following formula below, i am able to get the LY YTD for the exact month and day amount BUT when i fliter all the months the total LY YTD doesn't show correct amount, do you g...
  • StrategicSavvy's avatar
    StrategicSavvy
    2 years ago

    hi FR14 

     

    I belive you can use DATESYTD function based on your calendar table.  Make sure you have properly built di calendar table as Jihwan_Kim  mentioned before you go.

     

    First create measure that will make you are not showing values for future dates in your calendar table:

    ShowValueForDates :=
VAR LastDateWithData =
    CALCULATE (
        MAX ( OrderLine[Open_Date] ),
        REMOVEFILTERS ()
    )
VAR FirstDateVisible =
    MIN ( 'Date'[Date] )
VAR Result =
    FirstDateVisible <= LastDateWithData
RETURN
    Result

     

    Secondly create this measure:

     

    LY YTD Bookings = 
IF(
    [ShowValueForDates],
    CALCULATE(
        SUM ( OrderLine[Booking USD] ),
        DATESYTD('Date'[Date])
    )
)

     

    I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

    My LinkedIn: www.linkedin.com/in/lukasz-kozdron 

Jihwan_Kim's avatar
Jihwan_Kim
Icon for Super User rankSuper User
2 years ago

Hi,

I am not sure how your semantic model looks like, but I suggest using Calendar Dimension table to alter filter context.

But, by using only the situation shown here, please try something like below whether it produces the expected result.

 

LY YTD Bookings =
VAR _MaxDate =
    MAX ( OrderLine[Open_Date] )
VAR _PreviousYear =
    YEAR ( _MaxDate ) - 1
RETURN
    CALCULATE (
        SUM ( OrderLine[Booking USD] ),
        REMOVEFILTERS ( OrderLine[Open_Date] ),
        YEAR ( OrderLine[Open_Date] ) = _PreviousYear,
        MONTH ( OrderLine[Open_Date] ) <= MONTH ( _MaxDate ),
        DAY ( OrderLine[Open_Date] ) <= DAY ( _MaxDate )
    )
  • FR14's avatar
    FR14
    Icon for Helper I rankHelper I
    2 years ago

    Thank you but it didn't solve my problem unfotunately still the amount for the LY YTD to this day of the month is showing correct but then whe select JAN,FEB & March last year the total is not corrrect 😞