Filter table using previous day between Billing Start Date and Billing End Date

DAX DAX Calculations
1

I am trying to filter a table where the previous day is between the BillingPeriodStart and BillingPeriodEnd. I only want one row returned because I will create variables (MIN and MAX) for calculations.Filter using previous day

I don’t necessarily have to filter the table, I just need to grab the current Billing Start Date and Billing End Date based on yesterdays date. So if there is a way to do that, it will suffice.

2

@LASHBURN,

Lot of different ways to handle this one, but I went with the tried-and-true disconnected table approach:

  1. Created a table called Disconn Date using VALUES( Dates[Date] )

  2. Used the resulting field Disconn Date[Date] in my date slicer

  3. Created the following 0/1 measure to calculate which (if any) of the rows the selected date fell within

  4. Set the filter pane on the visual to only show rows where the measure = 1

    InsideRange =

     IF(
    AND(
        [Harvest Date] >= SELECTEDVALUE( Data[Billing Start Date] ),
        [Harvest Date] <= SELECTEDVALUE( Data[Billing End Date] )
    ), 1, 0
   )

Here’s what it looks like all put together:

image

Note: you don’t need to include InsideRange in your visual - I did so just for illustrative purposes.

I hope this is helpful. Full solution file posted below.

3

Thanks Brian, I don’t have time to test it out right now but hope to later. Thanks again!

4

@LASHBURN,

No rush. And easily adaptable if your inputs are coming from a column rather than a slicer - I wasn’t sure based on the initial post.

  • Brian
5

Yes, the inputs are coming from a column in a table… I am thinking of creating calculated date columns so I can call them as variables in a virtual table.

6

@LASHBURN,

You could also do them as measures and still call them in a virtual table. The key question is whether you’re going to want to use those date columns in a slicer and/or as the axis of a visual? If so, they will have to be calc columns (or added in PQ).

Please let me know if you still need assistance with this. If so, please provide either your PBIX or some sample data, plus a mockup of the results you want to achieve.

Thanks!

  • Brian
7

I am so far deep in this project and unfortunately did not build the solution with date slicers. I ended up filtering the data tables themselves for current month. So rather than laying a date slicer for different pages of the report, I am having to go back and build the virtual tables differently to get my results now for billing period. Unfortunately, all of my measures are based on the entire month.
I expect what I will have to do is go back and create all new measures filtering for the current billing period That should work.
So, my challenge is to be able to determine and capture the current billing period start and end date so I can build the new measures using those dates.

virtual table
virtual table916×247 13 KB

8

So can I create a new calculated date column in the table based on the logic you provided without having to lay the filter of 1? That way I can use them in my new measures.

9

Thanks Brian, you got me out of the weeds!
I created two measures to use

Current Billing End Date = CALCULATE(MAX(‘Projected Revenue’[BillingPeriodEnd]),FILTER(‘Projected Revenue’,[Inside Range]=1))

Current Billing Start Date = CALCULATE(MAX(‘Projected Revenue’[BillingPeriodStart]),FILTER(‘Projected Revenue’,[Inside Range]=1))

10

@LASHBURN,

That’s great – I was just writing you back with an additional question, but it looks like you’ve solved it. :+1:

  • Brian