Forum Discussion
Days Remaining Calculation Until Measure Value Reaches Zero
I'm trying to calculate the number of days until each account reaches zero (so we can anticipate ahead of time and either add money or transfer to avoid a problem). This report contains a date DIM table with several FACT tables, and I created a measure to sum over time the amount of money we've spent vs. the amount of money we have for each account for the FY. I can show the percent spent over time in a conditionally formatted matrix and hope someone will catch the date where planned + actual expenses will indicate the balance will reach or fall below zero. That's why I conditionally formatted anything below 50% as pink, and below 20% as Red.
I'd rather have a single column showing the number of days until the balance is less than or equal to zero. That seems like the best way to present to our fund managers and leadership which accounts need attention.
The values shown on the attached screengrab is a measure called %Spent. I've included the related measure and underlying table calculations off to the side in case that helps.
Trying to do this is way above my above my ability but I solve almost all of my problems by reading through this forum. Thanks in advance for any help that is offered :)
George
5 Replies
So for each account you want a simple number that represents the number of days until it runs out of funds?
Should this be viewed in a table as depicted in your screenshot or is it sufficient to show the number per account as of current date?
Good question and my apologies for not being more clear. I currently have the columns field as the Start of Week Date, hence the number of columns. I'd like to replace that with the number of days until balance reaches zero and then sort it from least to greatest as an indicator of which accounts need attention the most (and how soon) before it becomes critical.
George
hi, Anonymous
Please share your simple sample pbix file or some data sample and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
And I also have done a demo pbix,
For example:
I want to count days of measure value less than or equal to 10
Measure 2 = var _table= SUMMARIZE('Date','Date'[Date],"runcal",[Measure]) return CALCULATE(COUNTROWS(FILTER(_table,[runcal]<=10)))Result:
here is pbix file, please try it.
hope it could help you.
Best Regards,
Lin
Best Regards,
Lin
Hi Lin,
For all of my accounts, I have been showing the percent of the original balance remaining across the FY. What I really want to do is replace these date columns with a single column showing the number of days (from today) until account balance reaches 0% remaining.
Since we have hundreds of accounts that need to be managed, having table formatted this way would make it much easier to prioritize which accounts need attention first. The sample below is what I want - to change the first table into the second one. Hope this makes sense...
hi, Anonymous
This is the logic I mentioned above, You could try that demo pbix file. Change [Measure] to [% Remaining] and set conditional to greater than 0 in your own pbix file.
And from your screenshot, why there are 5 months of Account 1 that remaining >0 result is 180 but there are 1 months of Account 3 that remaining >0 result is 30?
If not your case, please share your simple sample pbix file or some data sample and expected output.
Best Regards,
Lin
