Time Based Measure- within 8 week range

[Anonymous]

Hello

I have a report which has a column for whether or not an order was received (column is Y/N or blank). This will be compared to a start date column, and I'm hoping to create a measure which will show (in a visualization) that if the order received column is blank or "N", and it is within 8 weeks (today's date compared to start date column) it will show those projects.

Not sure where to start, I was thinking of doing a multiple IF formula to sort out only the blanks and "N" and then compare the result of that to within 8 weeks, but is there a way for BI to calculate automatically today's date?

In the future I might add a new column that lists the date the order was received and compare that date with the start date column. 

[MFelix · Accepted solution]

Hi @Anonymous,

Add the following measure to your model:

Measure =
VAR OrdersSelected =
    MAX ( orders[OrderReceived] )
VAR DateSelected=
    MAX ( orders[Start Date ] )
RETURN
    IF ( DateSelected>= TODAY () - 56; IF ( OrdersSelected= "N" || OrdersSelected= BLANK (); 1; BLANK () ) )

Then add this measure as a filter to your visual and select that is equal to one should give you the expected result.

Top table is information not filtered and the bottom is filtered by the measure.

Regards,

MFelix