Forum Discussion

FR14's avatar
FR14
Helper 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 guys know what condition should be added to this DAX to also have the total LY YTD correct when we filter all the months?
 
LY YTD Bookings =
VAR _MaxDate = MAX(OrderLine[Open_Date])
VAR _PreviousYear = YEAR(_MaxDate) - 1
RETURN
CALCULATE(
    SUM(OrderLine[Booking USD]),
    YEAR(OrderLine[Open_Date]) = _PreviousYear,
    MONTH(OrderLine[Open_Date]) <= MONTH(_MaxDate),
    DAY(OrderLine[Open_Date]) <= DAY(_MaxDate)
)
 
 
Thank you all so much for your support.
  • 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 

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super 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
      Helper 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 😞

  • StrategicSavvy's avatar
    StrategicSavvy
    Resolver II
    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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi FR14 ,

    Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

    If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

    How to Get Your Question Answered Quickly 

    Regards,

    Xiaoxin Sheng