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 ) )
Anonymous
Please mock up some examples of your desired result. Say one if you pick 3 months and one if you pick 9 months. What do you want the report to show exactly?
- Anonymous4 years agoNot applicable
Thank you so much for showing me how this is done, I am extremely grateful.
I have my table of (People) which has their name and date of birth.
These are member of the band AC/DC for the example.
BirthDay | Name
09/07/1946 | Bon Scott
31/03/1955 | Angus Young
06/01/1953 | Malcolm Young
02/03/1956 | Mark Evans
14/12/1949 | Cliff Williams
19/05/1954 | Phil Rudd
05/10/1947 | Brian Johnson
If I use pick 3 months14/12/1949 | Cliff Williams
06/01/1953 | Malcolm Young
If I use pick 9 months14/12/1949 | Cliff Williams
06/01/1953 | Malcolm Young
31/03/1955 | Angus Young
02/03/1956 | Mark Evans
19/05/1954 | Phil Rudd
09/07/1946 | Bon Scott