cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mrcooper Frequent Visitor
Frequent 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
Super User
Super User

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

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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
mrcooper Frequent Visitor
Frequent Visitor

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

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.

Super User
Super User

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

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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Pedzilla Member
Member

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

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 Super Contributor
Super Contributor

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

@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"
)
mrcooper Frequent Visitor
Frequent Visitor

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

How do I handleany nulls?

Super User
Super User

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

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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Sean Super Contributor
Super Contributor

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

@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

mrcooper Frequent Visitor
Frequent Visitor

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

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

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)