DAX - Half Year YTD

_{Wednesday, June 6, 2018 10:39 PM}

Hello All,

Need urgent help. I need to calculate half year YTD. For e.g if we the year starts in January and June is mid year then for Sep, we would like to show sales from July to Sep of that year and if we are in May of that year it should show cumulative sales from Jan to May of that year.

Hope that makes sense.

Thanks

_{Thursday, June 7, 2018 4:27 AM ✅Answered}

Hi djs231,

Thanks for your question.

Assume you have a table called SalesAmount with two column Date and Amount, see below sample data:

Then, you can create SUMAmount and HalfYTD as below DAX formula:

SUMAmount = 
SUM( SalesAmount[Amount] )

HalfYTD =
IF (
    MONTH ( MAX ( SalesAmount[Date] ) <= 06,
    CALCULATE (
        SUM ( SalesAmount[Amount] ),
        FILTER (
            ALL ( SalesAmount[Date] ),
            YEAR ( SalesAmount[Date] ) = YEAR ( MAX ( SalesAmount[Date] ) )
                && SalesAmount[Date] <= MAX ( SalesAmount[Date] )
        )
    ),
    CALCULATE (
        SUM ( SalesAmount[Amount] ),
        FILTER (
            ALL ( SalesAmount[Date] ),
            YEAR ( SalesAmount[Date] ) = YEAR ( MAX ( SalesAmount[Date] ) )
                && SalesAmount[Date] <= MAX ( SalesAmount[Date] )
                && SalesAmount[Date] >= DATE ( YEAR ( MAX ( SalesAmount[Date] ) ), 07, 01 )
        )
    )
)

Best Regards
Willson Yuan
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com