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
Fowmy's avatar
Fowmy
Super User
5 years ago

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






craigdent's avatar
craigdent
Frequent Visitor
5 years ago

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.

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    @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

    • craigdent's avatar
      craigdent
      Frequent Visitor
      5 years ago

      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.

      • v-lili6-msft's avatar
        v-lili6-msft
        Community Support
        5 years ago

        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