Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Date Between DAX Formula

Hi Experts   How would you amend the following dax, so it gives me dates bewteen the follwoing ranges 1. 1 -7 days 2. 8 - 30 days 3 31 - 60 days 4 61 - 9 days   DatePeriod = UNION ( ADDC...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    hi Boca

     

    i solved the problem (see dax below)

    DatePeriod =
    UNION (
    ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today() ,today()+ 06) ), 'Date'[Date]),"Period","Next 07 Days") ,
    ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today() + 07,today()+14 ) ), 'Date'[Date]),"Period","Next 08-15 Days") ,
    ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today() + 15,today()+29) ), 'Date'[Date]),"Period","Next 16-30 Days") ,
    ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today() + 30,today()+59) ), 'Date'[Date]),"Period","Next 31-60 Days") ,
    ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today() + 60,today()+89) ), 'Date'[Date]),"Period","Next 61-90 Days") ,
    ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date'), 'Date'[Date]),"Period","Overall")
    )
Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi,

 

what would you like your result to look like? It seems like you want to create a new table, based on your date table, right?

I would try the following approach

DatePeriod = ADDCOLUMNS('Date', "Date Period", 
    IF(DATEDIFF(TODAY(), 'Date'[Date], DAY) <= 7, "1 - 7 days",
    IF(DATEDIFF(TODAY(), 'Date'[Date], DAY) > 7 && DATEDIFF(TODAY(), 'Date'[Date], DAY) <= 30, "8 - 30 days",
...)))

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Boca

 

I would like to follow the same format i used in the my original dax but with your logic for the date ranges....i.e UNION etc...i am using the date ranges as a filter. i works just fine as normal but stuck on the element on how to add date ranges step into the current dax i am using.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Alright, gotcha.

    It should work if you just delete the SUMMARIZE statement.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hi Boca

       

      i solved the problem (see dax below)

      DatePeriod =
      UNION (
      ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today() ,today()+ 06) ), 'Date'[Date]),"Period","Next 07 Days") ,
      ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today() + 07,today()+14 ) ), 'Date'[Date]),"Period","Next 08-15 Days") ,
      ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today() + 15,today()+29) ), 'Date'[Date]),"Period","Next 16-30 Days") ,
      ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today() + 30,today()+59) ), 'Date'[Date]),"Period","Next 31-60 Days") ,
      ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today() + 60,today()+89) ), 'Date'[Date]),"Period","Next 61-90 Days") ,
      ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date'), 'Date'[Date]),"Period","Overall")
      )