Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Help required with a slicer. To report a range.

Hi, I have an issue that I can't get my head around, because the problem has just been made extra difficult, from what I am experiencing. The problem, is I have a data set that has records with d...
  • jdbuchanan71's avatar
    4 years ago

    Thank you, that makes sense.

    The cleanest way I could see to do it is to add a column to the People table that calculates their next birthday.  This column will update when the model is refreshed so as peoples birthdays pass the [Next Birthday] column will shift to next year.

    Next Birthday = 
    VAR _Today = TODAY()
    VAR _YearToday = YEAR ( _Today )
    VAR _ThisYear = DATE ( _YearToday, MONTH ( People[Birthday] ), DAY ( People[Birthday] ) )
    VAR _NextYear = DATE ( _YearToday +1, MONTH ( People[Birthday] ), DAY ( People[Birthday] ) )
    RETURN 
    IF ( _ThisYear < _Today, _NextYear, _ThisYear )

    I added Snoop so I would have an upcoming October birthday for testing.

    Then we need a measure to check if the next birthday is in the upcoming months based on the users selection in the what if slicer.

    Birthday Check = 
    VAR _Months = [Months Value]
    VAR _EndDate = EOMONTH(TODAY(),_Months)
    RETURN
        CALCULATE(
            COUNTROWS(People),
            People[Next Birthday] <= _EndDate
        )

    We put the people and thier next birthday in a table and add the [Birthday Check] measure as a filter on the visual and set it to 'is not blank'.

    Which gives me the result you are looking for. 

    I have updated my sample file and attached it for you to look at.

     

     

     

     

  • jdbuchanan71's avatar
    4 years ago

    Anonymous 

    I wanted to figure out how to do it with just a measure so you would not have to add a column to the People table and rely on the model refresh to calculate the next birthday.  This measure will do the calculation every time it is checked so it should always return the up-to-date results.

    Birthday Check = 
    VAR _Months = [Months Value]
    VAR _EndDate = EOMONTH(TODAY(),_Months)
    VAR _People = 
        ADDCOLUMNS(
            SUMMARIZE(People,People[Name],People[Birthday]),
            "@Next Birhtday",
                VAR _Today = TODAY()
                VAR _YearToday = YEAR ( _Today )
                VAR _ThisYear = DATE ( _YearToday, MONTH ( People[Birthday] ), DAY ( People[Birthday] ) )
                VAR _NextYear = DATE ( _YearToday +1, MONTH ( People[Birthday] ), DAY ( People[Birthday] ) )
            RETURN 
                IF ( _ThisYear < _Today, _NextYear, _ThisYear )
        )
    RETURN COUNTROWS ( FILTER ( _People, [@Next Birhtday] <= _EndDate ) )