topic Help with dates between two rows in Desktop

Help with dates between two rows

craigdent — Wed, 30 Sep 2020 13:58:07 GMT

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 columns to show the first and last dates of each "month" but that hasn't worked so far.

So for example, I'd want to select November 2020 on a slicer dropdown, and get every approved record between the 4th October and 31st October, for December is would be 1st November to 28th November, etc.

Is this possible?

Currently there is a date table which has a relationship to the Date column in the cut off table. The records I'd like to show are in another table which also has a relationship to the data table for a columns with an approved date.

Re: Help with dates between two rows

Fowmy — Wed, 30 Sep 2020 14:24:26 GMT

craigdent

You can have two measures with Start and End dates or use these techniques with FILTER function to filter the table.

Start Date = VAR _Date = SELECTEDVALUE(Table9[Date]) RETURN CALCULATE( MAX(Table9[Date]), FILTER( ALL(Table9), Table9[Date] < _Date ) )

End Date = SELECTEDVALUE(Table9[Date])

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube LinkedIn

Re: Help with dates between two rows

amitchandak — Wed, 30 Sep 2020 14:40:51 GMT

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

Re: Help with dates between two rows

craigdent — Thu, 01 Oct 2020 10:39:30 GMT

Thank you Fowmy

I've tried to use this, would Table9 be the table that stores the cut off dates in my snip? Or is this the Date table itself?

I tried using that table (Overtime Deadlines) but it didn't bring anything up when I used it.

Re: Help with dates between two rows

craigdent — Thu, 01 Oct 2020 10:43:23 GMT

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?

Re: Help with dates between two rows

Fowmy — Thu, 01 Oct 2020 10:49:16 GMT

@craigdent

I did not take any dates table into my formula. You wanted to filter the table that you showed in the question based on the logic you mentioned. These formulas can be used to filter other tables. Why do not share a sample PBIX file with some sample data to check? Also, mention the expected result.

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube Linkedin

Re: Help with dates between two rows

amitchandak — Thu, 01 Oct 2020 11:29:14 GMT

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

Re: Help with dates between two rows

craigdent — Thu, 01 Oct 2020 14:02:33 GMT

My .pbix can be found here: Overtime Report - wasn't sure how to attached a file as I kept getting an error saying the file type is not supported!

Ideally, what I'd like is a dropdown slicer at the top with the Month from the Overtime Deadlines table which the filters the table based on the ApprovedDatestamp - the ApprovedDatestamp must be before the Date that the Month relates to, but after the previous Date above it.

Re: Help with dates between two rows

v-lili6-msft — Mon, 05 Oct 2020 02:30:08 GMT

hi craigdent

I'm a little confused by your description, what is your expected output in this sample pbix file.

Can you give me an example based the data.

Regards,

Lin

Re: Help with dates between two rows

craigdent — Mon, 05 Oct 2020 09:13:30 GMT

Hi Lin,

What I'm looking for is a slicer visual which can select from the Date field in the Overtime Deadlines table, which will then filter the main Table visual for those records with an ApprovedDatestamp before the selected date, but on or after the previous Date.

Re: Help with dates between two rows

v-lili6-msft — Tue, 06 Oct 2020 09:43:02 GMT

hi craigdent

Could you show us the expected output with an example in your sample pbix file, that will be a great help.

Regards,

Lin