Expected turnover per month

[Brandaris]

Hello PowerBI specialists,

I hope you will have the "golden solution" for my challenge:

I am looking for the (set of) DAX formula(s) (with usage of VAR or something), to  calculate the following:

the expected turnover per remaining month up to and including December of this year, calculated from a variable date.

The data I have is the following:

1. The start dates of  new possible customers: for example Prospect A, September 18, 2025

2. The expected turnover of every prospect up to and including the end of the year, for example € 100,000 for Prospect A

Now I would like to have the expected turnover for the individual months September, October, November and December. So an even distribution of that € 100,000 over the period after September 18. Do you have the "golden" solution for me for this challenge perhaps? 

I've tried some formulas with help of Copilot, but unfortunately it didn't work. One message I received was, for example: 

The expression refers to multiple columns. Multiple columns cannot be converted but a scalar value.

Thank you very much in advance for your help

 Hans

[v-kathullac]

Hi @Brandaris ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithanya.

[v-kathullac]

Hi @Brandaris ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithanya.

[v-kathullac]

Hi @Brandaris ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Regards,
Chaithanya.

[Anonymous]

Hi @Brandaris,

Thank you for reaching out to Microsoft Fabric Community Forum.

I have taken sample data for three months. Please test this query.

ExpectedTurnoverPerMonth =
VAR StartDate = SELECTEDVALUE('sample'[Start Date])
VAR TotalTurnover = SELECTEDVALUE('sample'[Expected Turnover (€)])
VAR EndOfYear1 = DATE(YEAR(StartDate), 12, 31)
VAR RemainingMonths = DATEDIFF(StartDate, EndOfYear1, MONTH) + 1
RETURN
IF(
MAX('DateTable'[Date]) >= StartDate && MAX('DateTable'[Date]) <= EndOfYear1,
TotalTurnover / RemainingMonths,
0
)

Here is the sample output:

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Regards,
Vinay Pabbu

[Brandaris]

Hello v-vpabbu,

Thanks for your help, but I'm afraid I didn't explain my questio good enough.

In my example Prospect A will start on 18. september 2025, so there are 104 days remaining this year.

So the real outcome should be as follows:

I think we have to make a table with dates (or Months) after the first delivery date (i.c. 18 september) until 31 december, so the first (partitial) month included, with per Prospect the expected turnover in the 4 last months of 2025

With kind regards,

Brandaris

[v-kathullac]

Hi @Brandaris 

I’ve taken a sample dataset and applied the above logic to it.
Attaching a screenshot for your reference.

Let me know if you need any changes or further details.
Happy to help!

Regards,

Chaithanya.

[lbendlin]

an even distribution based on what? Months? Days? Workdays?

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Brandaris]

Dear Ibendlin,

Thank you very much for your response and your request for a data model.
I had created a small set of data with Excel to show what I would like to have in PowerBI.
Unfortunately, it appears not to be possible to upload an Excel file. I then tried with a table, but it became more than 20,000 characters. Is there perhaps another way to send it? 

The expected turnover is always invoiced on the last day of the month, so the entire monthly turnover can be booked on the ENDOFMONTH. The first month in which the customer will be invoiced is therefore only a part of the entire month.
Hopefully, based on this information, you have a good tip for me on how to tackle this in various steps.
Thank you very much in advance.
Kind regards,
Hans

[lbendlin]

Please use the first link in my reply.