Forum Discussion

LMa59's avatar
LMa59
New Member
2 years ago

Average WCR

Hello,   My client asked me to calcule the average Working Capital Requirement. First of all, I explained how I calculate the WCR. I calculated the balance of debit and credit. I have a table of ...
Need Help
Shaurya's avatar
Shaurya
Icon for Memorable Member rankMemorable Member
2 years ago

Hi LMa59,

 

Try using this:

 

EC_solde = 
VAR StartDate = MIN('d_Calendar'[Date])  // Adjust based on your date column
VAR EndDate = MAX('d_Calendar'[Date])    // Adjust based on your date column

RETURN
CALCULATE(
    SUM('f_F_ECRITUREC'[Montant]),
    'f_F_ECRITUREC'[Sens] = 1,
    'd_Calendar'[Date] >= StartDate,
    'd_Calendar'[Date] <= EndDate
) - CALCULATE(
    SUM('f_F_ECRITUREC'[Montant]),
    'f_F_ECRITUREC'[Sens] = 0,
    'd_Calendar'[Date] >= StartDate,
    'd_Calendar'[Date] <= EndDate
)
Average_WCR = 
VAR SelectedStartDate = MIN('d_Calendar'[Date])  // Adjust based on your selection
VAR SelectedEndDate = MAX('d_Calendar'[Date])    // Adjust based on your selection

VAR NumberOfMonths = DATEDIFF(SelectedStartDate, SelectedEndDate, MONTH) + 1

RETURN
DIVIDE(
    CALCULATE(
        SUM('Mesures'[Solde]),
        'd_Calendar'[Date] >= SelectedStartDate && 'd_Calendar'[Date] <= SelectedEndDate
    ),
    NumberOfMonths,
    0
)

 

Works for you? Mark this post as a solution if it does!
Check out this blog of mine: How to Export Telemetry Data from Azure IoT Central into Power BI  

  • LMa59's avatar
    LMa59
    New Member
    2 years ago

    Thank you Shaurya for your answer!

    Unfortunetely it didn't work
    It calculates well my WCR but the average WCR it didn't work. With the formula it calculates the balance of the WCR at the end of March (so from begin of year until end of March) and then divide by the number of months so 3. But my problem here is that I want to sum the balance at the end of January + the sum at the end of February and so on.. 
    For example the balance at the end of January is 100, at the end of February is 50 (balance from 1st of January to end of February) and end of March is 80 (balance from 1st of January to end of March). The formula should sum 100 + 50 + 80 and divide by 3. In your formula it takes only 80 and divide by 3.

    I hope you could help me again or someone else. It is a tough one...