Having a dynamic flag depending on selected date

[Anonymous]

Hi,

I'm new at Power BI and got asked to solve a task my co-worker has.

The data model is for a restaurant chain that wants to be able to compare different locations, years and months with a flag to be able to remove locations that weren't open yet for the time of the comparison.

The data model has three tables:

• CompareYears (stand alone table with no relations to other tables)
  • CompareYear (field)
    • Has 10 values, years from 2011 - 2020 (2011, 2012... 2020)
• Venues (table)
  • OpeningDate (field)
    • Date for when the restaurant opened
  • Restaurants (field)
    • The name of the restaurants/locations
• Date Table

The task is to create a filter that can be used when comparing years.

So for an example:

When April 2021 is selected in the Date Table, and 2018 is selected in the CompareYears table. Only restaurants that opened BEFORE April 2018 should be shown when this filter is active.

I tried scripting this with creating a measure in the Venures table looking something like this:

FlagOpeningDate = 

VAR CY = 
    CALCULATETABLE(
        VALUES('Venues'[Restaurant]),
        FILTER('Venues',YEAR('Venues'[openingDate]) < (MAX('CompareYear'[CompareYear]))),
        FILTER('Venues',MONTH(Venues[openingDate]) < (MAX('Date Table'[Month])))
    )
RETURN

IF(
    COUNTROWS(INTERSECT(CY,VALUES('Venues'[Restaurant]))) > 0, 1,0
)

 But it didn't work. How is it best to solve this?

Thanks in advance!

Sincerely
Ash

[Anonymous]

Hi,

I got it working with daxer's solution.

Thanks for the help!

Sincerely
Ash

[Anonymous · Accepted solution]

// Create a filtering measure that
// will return 1 for the restaurants
// you want to see and 0 otherwise.
// Then, when you display your restaurants
// in a table or matrix, just put this
// measure in the Filter Pane of the visual
// and set the "is" condition to 1.

[Show Restaurant?] =
IF( ISINSCOPE( Venue[Restaurant] ),
    var OpenDate = SELECTEDVALUE( Venue[OpeningDate] )
    var ComparisonYear = SELECTEDVALUE( CompareYear[CompareYear] )
    var BaseMonthNumber = MONTH( MIN( 'Date Table'[Date] ) )
    var CutoffDate = DATE( ComparisonYear, BaseMonthNumber, 1 )
    var ShouldShow = OpenDate < CutoffDate
    return
        INT( ShouldShow )
)

[Anonymous]

Hi daxer,

This seems to be quite close to the solution. But I can still see 1's on the rows before the restaurant opened when I add the fields to a straight table as seen below. The restaurant in Berlin opened in May 2019 (0 on the far right on that row).

And is it possible to add this as an optional filter? So that it is triggered when a button in a box is pressed?

Sincerely,

Ash

[v-shex-msft]

Hi @Anonymous,

Can you please share the sample pbix file to test? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[Anonymous]

Venue[Restaurant] is assumed in my code to be the unique identifier of a restaurant. This field can't have the same value across different restaurants. What's more, a restaurant must have exactly one opening date and if this is not the case in the code I've given you, SELECTEDVALUE will return BLANK and hence the comparison OpenDate < CutoffDate will return TRUE, which means such a row will be displayed.

You have to adjust the code to your model for this to work correctly.

[sayaliredij]

Hi,

You can use the following formula to count restaurants opened 

Restaurant Count =

var selectedyear = SELECTEDVALUE('Year'[Year],2021)

RETURN

CALCULATE(COUNTROWS('Table') , 'Table'[Opening Date] < DATE(selectedyear,12,31))

Best,

Sayali

If this post helps, then please consider Accept it as the solution to help others find it more quickly.