cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
emmanuelkamara1
Helper I
Helper I

Need help please.

I am struggling to write a Dax function that returns the outstanding balance after doing some filtration. In the dataset below, I want to get the outstanding balance after doing this filtration (current date-registration date-upfront_days_included >=30). Return all those outstanding balance after doing this ....today minus registration date minus upfront days included >=30). 

I basically need a dax function to do this for me. Find below a snapshot of the data. God bless you!!

 

 

questionnn.PNG

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@emmanuelkamara1 - 

 

I believe the DAX should be something like:

 

Measure = 
  VAR __Table = 
    ADDCOLUMNS(
      'Table',
      "Selector",(TODAY() - [registration_date_utc]) * 1. - [upfront_days_included]
    )
RETURN
  SUMX(FILTER(__Table,[Selector] >= 30),[Outstanding_balance])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

9 REPLIES 9
daxer
Solution Sage
Solution Sage

This is not clear. Please follow this post to maximize the chances of answers:

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
emmanuelkamara1
Helper I
Helper I

I want to calculate the potfolio at risk which is the outstanding balce for accounts that have been disabled for 30+ days divided by all the outstanding balnce of enabled and disabled accounts. When a user register an account, the user will get a minimum of 7 days to use the product before it switches to disbaled mode. However, the user will get more than 7 days to use the product if the upfront price is  grater than the minimum upfront price. To get an accurate Portfolio at risk, I will have to filter out new accounts because I will not know if they are bad or good customers that is why I needed to do some filteration. Before including an account in my PAR calculation, I need to make sure that the current date minus the registration date minus the upfront days is grater than or equal to 30.

Greg_Deckler
Super User
Super User

@emmanuelkamara1 - 

 

I believe the DAX should be something like:

 

Measure = 
  VAR __Table = 
    ADDCOLUMNS(
      'Table',
      "Selector",(TODAY() - [registration_date_utc]) * 1. - [upfront_days_included]
    )
RETURN
  SUMX(FILTER(__Table,[Selector] >= 30),[Outstanding_balance])

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

View solution in original post

Hi @Greg_Deckler , @daxer I want to calculate the potfolio at risk which is the outstanding balance for accounts that have been disabled for 30+ days divided by all the outstanding balance of enabled and disabled accounts. When a user register an account, the user will get a minimum of 7 days to use the product before it switches to disabled mode. However, the user will get more than 7 days to use the product if the upfront price is grater than the minimum upfront price. To get an accurate Portfolio at risk, I will have to filter out new accounts because I will not know if they are bad or good customers that is why I needed to do some filtration. Before including an account in my PAR calculation, I need to make sure that the current date minus the registration date minus the upfront days is grater than or equal to 30.

 

questionnn.PNG

@emmanuelkamara1 - So was my DAX formula correct because:

"Before including an account in my PAR calculation, I need to make sure that the current date minus the registration date minus the upfront days is grater than or equal to 30."

 

Unless I'm mistaken, that's what the DAX was doing.

 

Perhaps we need to backup. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Thanks for your time @Greg_Deckler , see below the output that I got. 

m.PNG

Thanks @Greg_Deckler , Works....I had to change the data-type .

 

Great!

@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

@emmanuelkamara1 - One, I would try it as a Card visual. Two, what does See details say?


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors