Forum Discussion
Solved
Using DATEADD() and cross-filtering
Hi All ! we have 2 simple datasets + Calendar: our screen: Our steps: 1. choice Products in slicer Prod or leave it unselected 2. choice month in slicer Month (one or mo...
I was modify my formula (using PARALLELPERIOD ) and it worked!
Nprev = VAR monthBack = (DATEDIFF(MIN('rsp'[date]),MAX('rsp'[date]),MONTH)+1)*-1 VAR xStart = STARTOFMONTH(PARALLELPERIOD(rsp[date],monthBack,MONTH)) VAR xEnd = ENDOFMONTH(PARALLELPERIOD(rsp[date],monthBack,MONTH)) VAR cntx = CALCULATE(COUNTROWS(txt),FILTER(ALL(rsp[date],rsp[month]),rsp[date]>=xStart && rsp[date]<=xEnd)) RETURN cntx
Thank you for your quick response!
My apologies for the delay in reply. Our office was closed for the weekend ((
I applied your idea to calculate for a displacement of the full month(s).
count_MonthBack =
VAR xDiffMonthBack = (DATEDIFF(MIN('calendar'[Date]),MAX('calendar'[Date]),MONTH)+1)*-1
VAR xStartBack = STARTOFMONTH(DATEADD('calendar'[Date],xDiffMonthBack,MONTH))
VAR xEndBack = ENDOFMONTH(DATEADD('calendar'[Date],xDiffMonthBack,MONTH))
VAR xCountBack=CALCULATE([cnt],FILTER(all(rsp),rsp[date]>=xStartBack && rsp[date]<=xEndBack))
RETURN xCountBack
Unfortunately, I couldn't do it. If we use the slicer Prod, everything breaks.
Perhaps the problem is in the DATEADD(). How can this be fixed for month metrics?
For check:
if we select the month 2021-04, then the date of the week should be filtered by 01.03.2021-31.03.2021 (one month)
if we select the month 2021-04&2021-03, then the date of the week should be filtered by 01.01.2021-28.02.2021 (two months)
Regards,
I was modify my formula (using PARALLELPERIOD ) and it worked!
Nprev =
VAR monthBack = (DATEDIFF(MIN('rsp'[date]),MAX('rsp'[date]),MONTH)+1)*-1
VAR xStart = STARTOFMONTH(PARALLELPERIOD(rsp[date],monthBack,MONTH))
VAR xEnd = ENDOFMONTH(PARALLELPERIOD(rsp[date],monthBack,MONTH))
VAR cntx = CALCULATE(COUNTROWS(txt),FILTER(ALL(rsp[date],rsp[month]),rsp[date]>=xStart && rsp[date]<=xEnd))
RETURN cntx