The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I would still consider myself fairly new to the Power BI world, although I have made some fabulous dashboards and reports for our company. Most of the data I pull from the ODATA feed is already formatted and massaged to the point where I haven't had to use many complex DAX or formulas or anything along those line. I have done a new column from measure and was able to get that syntax down, but it was a pretty simple formula.
Anyhow, what I am trying to accomplish seems simple, but looks like it may require some more complex configuration. If someone could help out it would be much appreciated.
I am working on an AR Aging Report. I have a column called "DaysPastDue" that is full of numbers ranging from -70 to 393. What I am trying to accomplish is either creating a column or a hierarchy (whichever makes the most sense in this situation. The formula would be something along these lines:
If DaysPastDue is <=0 then the output would be "Current"
If DaysPastDue is >=1 and <= 30 then the output would be "1-30 Days Past Due"
If DaysPastDue is >=31 and <= 60 then the output would be "30-60 Days Past Due"
If DaysPastDue is >= 61 and <= 90 then the output would be "60-90 Days Past Due"
If DaysPastDue is >=91 then the output would be "Over 90 Days Past Due"
I am not sure what a hierarchy really even does at this moment, but if it could be useful for this the so be it. If it is easy to just create the formula properly I would appreciate the help. I am stumped at this point and this shouldn't be THAT difficut.
Output would be like this
DaysPastDue | PastDueStatus
_________________________________________
-2 | Current
64 | 60-90 Days Past Due
3 | 1-30 Days Past Due
45 | 30-60 Days Past Due
-47 | Current
0 | Current
97 | Over 90 Days Past Due
And so on.....
You are in luck. I helped someone with a similar issue previously and wrote a blog about it.
http://exceleratorbi.com.au/collections-reporting-using-power-pivot/
Matt,
This was a good article but not exactly what I am trying to accomplish and I am using Power BI Desktop. My intent is to to use this formula to create a slicer out of the column that is created. Or use it for visuals. I have the number, I just need to it to correlate to the DaysPastDue column. I literally need just as the post says.
Hi
How about Switch statement?
Age Band = switch(true(),
DaysPastDue <=0 , "Current",
DaysPastDue >=1 && DaysPastDue <= 30 "1-30 Days Past Due".......,)
Then you can use this column as a slicer.
Ped
@mrcooper SWITCH is internally converted into multiple IF statetements but its so much easier to write and follow
and you don't have all the opening ( and closing ) for each IF
This column shoud do the trick!
PastDueStatus COLUMN = SWITCH ( TRUE (), 'Table'[DaysPastDue] <= 0, "Current", 'Table'[DaysPastDue] > 0 && 'Table'[DaysPastDue] <= 30, "1-30 Days Past Due", 'Table'[DaysPastDue] > 30 && 'Table'[DaysPastDue] <= 60, "30-60 Days Past Due", 'Table'[DaysPastDue] > 60 && 'Table'[DaysPastDue] <= 90, "60-90 Days Past Due", "Over 90 Days Past Due" )
How do I handleany nulls?
@mrcooper How about this?
PastDueStatus COLUMN 2 = IF ( ISBLANK('Table'[DaysPastDue]), "No Data", SWITCH ( TRUE (), 'Table'[DaysPastDue] <= 0, "Current", 'Table'[DaysPastDue] > 0 && 'Table'[DaysPastDue] <= 30, "1-30 Days Past Due", 'Table'[DaysPastDue] > 30 && 'Table'[DaysPastDue] <= 60, "30-60 Days Past Due", 'Table'[DaysPastDue] > 60 && 'Table'[DaysPastDue] <= 90, "60-90 Days Past Due", "Over 90 Days Past Due" ))
Hope this helps!
I just finished putting it all together....
AR Status = SWITCH(true(),
ISBLANK('BI - GS Aged AR'[DaysPastDue2]), "No Data",
'BI - GS Aged AR'[DaysPastDue2] <=0 , "Current",
'BI - GS Aged AR'[DaysPastDue2] >=1 & 'BI - GS Aged AR'[DaysPastDue2] <=30 ,"1-30 Days Past Due",
'BI - GS Aged AR'[DaysPastDue2] >=31 & 'BI - GS Aged AR'[DaysPastDue2] <=60 ,"30-60 Days Past Due",
'BI - GS Aged AR'[DaysPastDue2] >=61 & 'BI - GS Aged AR'[DaysPastDue2] <= 90, "60-90 Days Pat Due",
'BI - GS Aged AR'[DaysPastDue2] >=91, "Over 90 Days Past Due"
)
It looks like we have a winner....Then it bombs with...
"DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values"
What format should the "DaysPastDue" column be in order for this to work without getting any trickier with the code?
@mrcooper Do you import the results in DayPastDue? Strange that they are not Number already?
For quick Visual reference of the Type in a Column => left-justified is TEXT and right-justified Number/Date?
They are imported, but in the system they come from they are calculated fields. It comes across as a decimal number.
I was thinking of duplicating that row and converting it then build the same formula with the updated name. The problem is, I don;t know what format the DaysPastDue2 and what the newly created column should be,
I would go with Whole Number - unless you are using date and time to calculate the difference and really getting decimal numbers.
But either number should work.
Still receive the same error even after converting to Whole Number and Text.
I told you, it seems simple but it just isn't working.
@mrcooper Numbers are Right-justified in the Column while Text is Left-justified as in the picture
Notice in the first 2 - the numbers are on the right side inside the column
while in the 3rd they are on the left side in the Column meaning (Text not Number) and that is where the ERROR occurs
If you convert the column you are referencing in the AR Status formula to Data Type Number it should work!
hi @mrcooper
can you post a screenshot of your column DaysPastDue showing part of his values and type of column.
There is an ISBLANK function that will handle nulls along with many other logical tests.
Ok, sorry about that. You need to create a banded column and then use it as a slicer.
You can use one of 2 techniques
http://exceleratorbi.com.au/banding-in-dax/
http://exceleratorbi.com.au/conditional-columns-power-bi-desktop/
The second one is newer and probably easier - you do it during data load
The second option is perfect, but not sure how to handle nulls that way.
@mrcooper or you can just include it in the SWITCH like this on top
PastDueStatus COLUMN 3 = SWITCH ( TRUE (), ISBLANK('Table'[DaysPastDue]), "No Data", 'Table'[DaysPastDue] <= 0, "Current", 'Table'[DaysPastDue] > 0 && 'Table'[DaysPastDue] <= 30, "1-30 Days Past Due", 'Table'[DaysPastDue] > 30 && 'Table'[DaysPastDue] <= 60, "30-60 Days Past Due", 'Table'[DaysPastDue] > 60 && 'Table'[DaysPastDue] <= 90, "60-90 Days Past Due", "Over 90 Days Past Due" )
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
95 | |
93 | |
85 | |
68 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |