locked
DAX - Half Year YTD RRS feed

  • Question

  • 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

    Wednesday, June 6, 2018 10:39 PM

Answers

  • 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

    • Proposed as answer by alexander fun Thursday, June 7, 2018 10:49 AM
    • Marked as answer by djs231 Thursday, June 7, 2018 4:05 PM
    Thursday, June 7, 2018 4:27 AM