Forum Discussion

Brandaris's avatar
Brandaris
Icon for Helper I rankHelper I
1 year ago

Expected turnover per month

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...
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
Brandaris
Icon for Helper I rankHelper I
1 year ago

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's avatar
    v-kathullac
    Icon for Community Support rankCommunity Support
    1 year ago

    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.