Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
2 questions, from the example data below, is there a way in DAX to be able to
1. find the number of people who gave some money in the last two years
2. Create 3 groups from the dataset, so all the people who gave within between "0-24 months", "25-48 months", then "Older"
People | Date | Amount |
person 1 | 01/01/2019 | 10 |
person 2 | 25/02/2002 | 20 |
person 3 | 13/05/2000 | 30 |
person 4 | 16/07/2018 | 40 |
person 5 | 17/08/2018 | 50 |
person 6 | 15/12/2009 | 60 |
person 7 | 23/01/2019 | 15 |
person 8 | 13/05/2019 | 16 |
person 9 | 06/05/2005 | 13 |
person 10 | 17/08/2006 | 24 |
person 11 | 26/09/2007 | 25 |
person 12 | 14/03/2017 | 29 |
person 13 | 16/07/2018 | 50 |
person 14 | 18/01/2001 | 31 |
person 15 | 12/12/2019 | 110 |
person 16 | 16/07/2019 | 40 |
Solved! Go to Solution.
@UK_User123456 Please try below calculated columns and let me know if you have any question
WithinLastTwoyear =
VAR _diff = DATEDIFF('Table'[Date],TODAY(),YEAR)
RETURN IF(_diff<=2,TRUE(),FALSE())
Group =
VAR _month = DATEDIFF('Table'[Date],TODAY(),MONTH)
RETURN SWITCH(TRUE()
,AND(_month>=0,_month<25),"0-24 months"
,AND(_month>=25,_month<49),"25-48 months"
,"Other")
@UK_User123456 Please try below calculated columns and let me know if you have any question
WithinLastTwoyear =
VAR _diff = DATEDIFF('Table'[Date],TODAY(),YEAR)
RETURN IF(_diff<=2,TRUE(),FALSE())
Group =
VAR _month = DATEDIFF('Table'[Date],TODAY(),MONTH)
RETURN SWITCH(TRUE()
,AND(_month>=0,_month<25),"0-24 months"
,AND(_month>=25,_month<49),"25-48 months"
,"Other")