Forum Discussion
Help required with a slicer. To report a range.
- 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.
- 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 ) )
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.
- Anonymous4 years agoNot applicable
Good choice adding Snoop, someone who shares his birthday with both Dannii Minogue and myself.