locked
DAX dynamic datesbetween() RRS feed

  • Question

  • Hi all,


    I was trying to make a DAX for a dynamic datesbetween(). The idea goes like:


    The user filters a date in the pivot table. That date is used as "ending date" for datesbetween(). I tried the following but it didnt work

    test1:=CALCULATE([Qty Stock];DATESBETWEEN(Datess[PK_Date];"01/01/2010";ALLSELECTED(Dates[PK_Date])))



    this measure should go to the beginning of the month (removing the firs 2 positions from the date and replacing them with 01) and to the selected date. The idea is also to make a cumulative amounts

    test2:=CALCULATE([Qty Stock];
                                                             DATESBETWEEN(Dates[PK_Date];CONCATENATE("01";RIGHT(ALLSELECTED(Dates[PK_Date]);LEN(ALLSELECTED(Dates[PK_Date]))-2))
                                                         ;ALLSELECTED(Dates[PK_Date])
                                                                                      )
                                                             )

    the DAX compiles OK but then excel raises error.

    thanks a lot for your help


    • Edited by the_txeriff Wednesday, March 27, 2013 2:40 PM
    Wednesday, March 27, 2013 2:39 PM

Answers

  • you could simply use TOTALMTD()-function to do the whole calculation for you

    it aggregates all Dates within the current month from the first day until the selected day


    - www.pmOne.com -

    • Marked as answer by the_txeriff Wednesday, April 3, 2013 8:04 AM
    Wednesday, March 27, 2013 9:56 PM
    Answerer

All replies

  • ALLSELECT returns all selected values. However, DATESBETWEEN, CONCATENATE, ... expect one value only.

    Did you want to use the LASTDATE function instead?


    The Data Specialist (Blog)

    Wednesday, March 27, 2013 5:21 PM
  • ALLSELECT returns all selected values. However, DATESBETWEEN, CONCATENATE, ... expect one value only.

    Did you want to use the LASTDATE function instead?


    The Data Specialist (Blog)

    Thanks for the response. yep probably lastdate is ok for the ending date but I need to know the date user clicked on the pivot table filter. How do I know that? is that posible to retrive the date and use it as starting date in the datesbetween() ?

    Wednesday, March 27, 2013 5:26 PM
  • you could simply use TOTALMTD()-function to do the whole calculation for you

    it aggregates all Dates within the current month from the first day until the selected day


    - www.pmOne.com -

    • Marked as answer by the_txeriff Wednesday, April 3, 2013 8:04 AM
    Wednesday, March 27, 2013 9:56 PM
    Answerer