Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Time Based Measure- within 8 week range

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. 

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

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.

 

sss.png

 

Regards,

MFelix

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

1 REPLY 1
MFelix
Super User
Super User

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.

 

sss.png

 

Regards,

MFelix

 

 

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.