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
Sorry, I was showing the [Total Amount] just to show that the [Filtered Months Amount] returns data for only the number of months the user selects. In your report you would not show the [Total Amount] measure, just the [Filtered Months Amount]. So if I pick 4 it shows me only current + previous 4:
And yes, you just need the date table to have the offset column.
- Anonymous4 years agoNot applicable
Hi jdbuchanan71
I'm really sorry I have no idea how it is working for you.
The reason I can't share the data, which I could have fixed up. It is a table of peoples birthdays within the company that I work for, the HR team want to have a list of people birthdays etc. Which is why they wanted the report just to be something that they can see for advanced warning.
So I have my table (People) which has the people and dates in.
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 JohnsonI have tried to do the measure, and it just wont let me. Strangely. However the Parameter slicer is working for me...
And this is quite a nice thing, so I can select how many months in advanced and choose multiples. It's not what HR want, but it is nice. So thank you for showing me this, I am very much impressed.