Forum Discussion

StenX's avatar
StenX
Frequent Visitor
4 years ago
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...
Need Help
  • StenX's avatar
    StenX
    4 years ago

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

StenX , Try like

 

same period based on date range
Last Period =
var _max =maxx(date,date[date])
var _min =maxx(date,date[date])
var datediff1 = datediff(_min,_max,day)
var _maxX = _max-datediff1
var _minX = _min -datediff1
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date),date[date]<=_maxX &&date[date]>=_minX)))

 

StenX's avatar
StenX
Frequent Visitor
4 years ago

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,

  • StenX's avatar
    StenX
    Frequent Visitor
    4 years ago

    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