LY YTD (total)

[FR14]

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.

[Anonymous]

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

[StrategicSavvy · Accepted solution]

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]

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]

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 😞