Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mrcooper
Regular Visitor

What I Thought would be simple...I need help!

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.....

17 REPLIES 17

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 is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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.

Anonymous
Not applicable

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

Sean
Community Champion
Community Champion

@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! Smiley Happy

 

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?

Sean
Community Champion
Community Champion

@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"
))

2016-10-22 - SWITCH - DaysPastDue.png

 

Hope this helps! Smiley Happy

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?

Sean
Community Champion
Community Champion

@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,

Sean
Community Champion
Community Champion

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.

Sean
Community Champion
Community Champion

@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!

 

2016-10-22 - SWITCH - DaysPastDue3.png

hi @mrcooper

 

can you post a screenshot of your column DaysPastDue showing part of his values and type of column.




Lima - Peru

There is an ISBLANK function that will handle nulls along with many other logical tests. 

https://msdn.microsoft.com/en-us/library/ee634552.aspx



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

The second option is perfect, but not sure how to handle nulls that way.

Sean
Community Champion
Community Champion

@mrcooper or you can just include it in the SWITCH like this on top Smiley Happy

 

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"
)

 

2016-10-22 - SWITCH - DaysPastDue2.png 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.