Forum Discussion

craigdent's avatar
craigdent
Frequent Visitor
5 years ago

Help with dates between two rows

Hi all,   I have a table with payroll cut off dates and would like to filter another table based on results between each cut off date. I'm not sure where to start, I've thought about creating colum...
Need Help
amitchandak's avatar
amitchandak
Super User
5 years ago

craigdent , Try like

 

measure =
var _Mtd = CALCULATE(Max(Table[Date]),DATESMTD('Date'[Date]))
var _lmt = CALCULATE(Max(Table[Date]),DATESMTD(dateadd('Date'[Date],-1,MONTH))) +1

return
calculate([measure], filter (all('Date') , 'Date'[Date]>= _lmt && 'Date'[Date]<=_Mtd))

craigdent's avatar
craigdent
Frequent Visitor
5 years ago

Thanks amitchandak,

 

I couldn't get this working either, I think because it assumes the next monthly cut off is the same day of the next month, however it varies depending on the lenght of the month and the next pay day (which could be earlier if it would fall on a weekend or bank holiday).

 

Also, the [measure] on the last line kept coming up with a circular error warning, should I ignore that?

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    craigdent ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.