locked
YTD that cuts dates off RRS feed

  • Question

  • Hello. I have a YTD function for sales. For 2016, sales go through Aug 2016. However, my date table goes through December so it runs the YTD through December with no increase after Aug. How can I get it to cut off after August and not show the month rows in pivot table? Gracias. 
    Wednesday, November 16, 2016 6:18 PM

Answers

  • Hi Alex,

    You can follow the pattern here:

    http://www.daxpatterns.com/cumulative-total/

    In the "Complete Pattern" section there is an example of a [Cumulative Quantity] measure that checks whether the date has gone beyond the end of the data and returns blank in that case.

    In your case, you probably want a measure like:

    Sales YTD =
        IF (
            MIN ( 'Date'[Date] ) <= CALCULATE ( MAX ( Sales[Date] ), ALL ( Sales ) ),
            CALCULATE ( SUM ( Sales[Sales] ), DATESYTD ( 'Date'[Date] ) )
        )


    Owen Auger, CFA https://nz.linkedin.com/in/owenauger


    Wednesday, November 16, 2016 10:30 PM