Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Having a dynamic flag depending on selected date

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

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago
    // 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 )
)

6 Replies

  • sayaliredij's avatar
    sayaliredij
    Solution Sage
    5 years ago

    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.
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago
    // 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's avatar
      Anonymous
      Not applicable
      5 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi,

    I got it working with daxer's solution.

     

    Thanks for the help!

     

    Sincerely
    Ash