13 Replies Latest reply: Aug 21, 2017 7:47 AM by Camille Menten RSS

    Graph with data Dec over different years + MTD for current year

    Camille Menten

      Hi all,

       

      I'm quite stuck in my set analysis for the following:

       

      I want to count the nrs of account we have in the month december of eacht year since 2014. For the current year, I want to have the nrs for the last month.

       

      I have tried different set-ups but none work.

       

      I have identified the max month per year throught following formula:

      Max({<IsActual_={1}>} Month)

       

      Combined with year it gives me the below as expected

       

       

      However, when I try combining this into the count of my accounts, it doesn't work.

       

      count( {<[Month] = {"$(=Max({<IsActual_={1}>}[Month]))"}> }distinct [DMU])

       

      I guess because Max({<IsActual_={1}>}[Month] is giving a numeric result (12 or 7) while my field Month is giving 'Dec' or 'Aug' as result.

       

      Then I tried

       

      if ( 

      Year = Year(Today()),

      count( {< Month = {'Month(Today())'} > } distinct [DMU]),

      Count( {< Month = {'Dec'} >} distinct [DMU])

      )

       

      this gives me all values but for 2017

       

      help is welcome ...