Forum Discussion
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 (
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")
)
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"))
7 Replies
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.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 ifADDCOLUMNS( SUMMARIZE( CALCULATETABLE('Date' , DATESBETWEEN('Date'[Date],today()>=0 && DATESBETWEEN('Date'[Date],today()<=7,today()) ), 'Date'[Date]),"Period","0 - 7 Days") would work...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))
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",
...)))
