Date Between DAX Formula

[Anonymous]

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 (

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-07+1,today()) ), 'Date'[Date]),"Period","Last 07 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-30+1,today()) ), 'Date'[Date]),"Period","Last 30 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-60+1,today()) ), 'Date'[Date]),"Period","Last 60 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()-90+1,today()) ), 'Date'[Date]),"Period","Last 90 Days") ,

ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date'), 'Date'[Date]),"Period","Overall")

)

[Anonymous]

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]

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]

Alright, gotcha.

It should work if you just delete the SUMMARIZE statement.

[Anonymous · Accepted solution]

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]

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

Appreciate your Kudos.

[Anonymous]

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]

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))