Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have have made a some formulas that tecnically work BUT I relize its not reporting the correct information.
I am trying to calculate how many months ist been since some data has been reviewed.
This formula works but i know its where i intruduce my first problem.
_Months_of_age = DATEDIFF(MAX(SIR_XLS[Rev_Date]),TODAY(),MONTH)
The problem is i have some null values in there - for some reason this wasnt creating any errors, so i kept on going with my next calculated colum.
This formula breaks the months this up into 4 different catecories:
_Months since reviewed 2 =
IF (
(SIR_XLS[_Months_of_age_2])< 13,
"12 or less",
IF (
AND ( (SIR_XLS[_Months_of_age_2])> 12, [_Months_of_age_2] < 25),
"12 – 24",
IF (AND ((SIR_XLS[_Months_of_age_2])> 26, [_Months_of_age_2] < 61),
" 25 – 60",
"61 + "
)
))
What I need it s catecory for null - At the moment is bulks out my "12 months or less" category because somehow I have confused it - is treating null as 0
I am a bit confused where and how I tell it about null values. Its import we know what percentage are null values AND how old things are. I thought if I could make null or isblank = -1 then I would be able to make this work....
Any help would be MOST welcome. I Need a Power BI Hero! 🙂
Pointers? Directions? Thoughts?
Kindest regards,
Marc.
Solved! Go to Solution.
Hi @steambucky,
What I need it s catecory for null - At the moment is bulks out my "12 months or less" category because somehow I have confused it - is treating null as 0
If I understand your requiremently that you want to have five categories,"null", "12 or less", "12 – 24", " 25 – 60", "61 + ".
If it is , you could modify your formula like below.
Column =
IF (
'Table1'[_Months_of_age] =BLANK(),
"null",
IF (
( 'Table1'[_Months_of_age] ) < 13,
"12 or less",
IF (
AND ( ( 'Table1'[_Months_of_age] ) > 12, [_Months_of_age] < 25 ),
"12 - 24",
IF (
AND ( ( 'Table1'[_Months_of_age] ) > 26, [_Months_of_age] < 61 ),
" 25 - 60",
"61 + "
)
)
)
)
And the output is below.
If you need additional help please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Best Regards,
Cherry
Hi @steambucky,
What I need it s catecory for null - At the moment is bulks out my "12 months or less" category because somehow I have confused it - is treating null as 0
If I understand your requiremently that you want to have five categories,"null", "12 or less", "12 – 24", " 25 – 60", "61 + ".
If it is , you could modify your formula like below.
Column =
IF (
'Table1'[_Months_of_age] =BLANK(),
"null",
IF (
( 'Table1'[_Months_of_age] ) < 13,
"12 or less",
IF (
AND ( ( 'Table1'[_Months_of_age] ) > 12, [_Months_of_age] < 25 ),
"12 - 24",
IF (
AND ( ( 'Table1'[_Months_of_age] ) > 26, [_Months_of_age] < 61 ),
" 25 - 60",
"61 + "
)
)
)
)
And the output is below.
If you need additional help please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)
Best Regards,
Cherry
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |