power bi averages

AI Training: For Busy Decision Makers & Professionals Book now

Forum home » Delegate support and help forum » Microsoft Power BI training and help » Power BI - averages and calculations for forecasting

Power BI - averages and calculations for forecasting

resolvedResolved · Urgent Priority · Version 2016

Power BI - averages and calculations for forecasting

Hi,

Hope you are all keeping well, Wonder if you can help?

Im working with income and payment data to build a dashboard for my receivables function.

They would like to be able to see the likelihood of an invoice being paid within the current month, based on historical payment data and days left in the month.

My dashboard shows days left in the month, average days to pay for each customer (where they have had prior invoices), how many days old each invoice is and what the age of each invoice will be at month end. What i would like to show is if the invoice age is -

< than average days to pay - "NO"
> than average days to pay but by less than 31 - "YES"
if ages at month end is > 31 days above average the "CALL"

Client No. / Client Name / Outstanding / Due date / Average Days to pay / Days aged at month end.


Average days to pay is a format on the visual not a column in the dataset.

hope this makes sense?


Thanks

Helen

RE: Power BI - averages and calculations for forecasting

Hi Helen,

The question is clearly formulated. However, could you elaborate on what you mean when you say: "The Average days to pay is a format on the visual, not a column in the dataset"?

Thank you

Ron Oldeboom
Learning & Development Consultant

RE: Power BI - averages and calculations for forecasting

Hi,

Sorry, that wasn't very clear.

I just mean its formatted as an average from the visualizations values from the actual days to payment on the historical paid invoices. Its not a column called Average days to pay but "Days to pay" shown on the table as an average.

Cheers

H

RE: Power BI - averages and calculations for forecasting

Hi Helen,

Sorry that you have had to wait for an answer.


You will need to make a measure which will calculate how many days left in current month.

days Left in Month = COUNTROWS(
CALENDAR(
TODAY(),
EOMONTH(TODAY(), 0)
)
)


Create a calculated column in the table, and type:

=if([days left in month]<Average([actual days]),"No",if(and([days left in month]>average([actual days]),"Yes",if([ages at month end is]-average([actual days])>31,"Call")))

I hope that I understood your logic and this is what you want.


Kind regards

Jens Bonde
Microsoft Office Specialist Trainer

Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector

Mon 13 Apr 2020: Automatically marked as resolved.

 

Training courses

 

Welcome. Please choose your application (eg. Excel) and then post your question.

Our Microsoft Qualified trainers will then respond within 24 hours (working days).

Frequently Asked Questions
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Power BI tip:

Page and Tab Shortcuts

Ctrl + Page Up/Page Down: Navigate between pages.
Ctrl + T: Create a new page.
Ctrl + Shift + F10: Toggle between fields pane and report canvas.

View all Power BI hints and tips


Server loaded in 0.11 secs.