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")
    )
amitchandak's avatar
amitchandak
Super User
6 years ago

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Amit

     

    the first line of DAX is 

    DatePeriod =
    UNION (
    ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-07+1,today()) ), 'Date'[Date]),"Period","Last 07 Days") 
     
    I would like to have dates in the range >=0 && <= 7 as opposed to dates in the period of less then 7 days only. 
     
    not sure if 
    ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()>=0 && DATESBETWEEN('Date'[Date],today()<=7,today()) ), 'Date'[Date]),"Period","0 - 7 Days") would work... 
    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Check if these can work

      Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date',today()-7,7,day))
      Rolling 7 to 14 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date',today()-14,7,day))