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 mapping with all the accounts but only with the 3 first account number and a column where I specify if it is necessary for the WCR or not and I have a table from Sage with the entire accouts number.
As my client needs to remove specific accounts (entire accounts numbers coming for the comptability table from Sage) 

As I had difficulties with my formula I filtered my card visual with only accounts used for WCR (mapping table) and remove some specific accounts he requested

 

My formula for the balance is : CALCULATE('Mesures'[Solde], 'd_Calendar'[Year])
EC_solde formula is :

CALCULATE(SUM('f_F_ECRITUREC'[Montant]), 'f_F_ECRITUREC'[Sens] IN { 1 }) - CALCULATE(SUM('f_F_ECRITUREC'[Montant]), 'f_F_ECRITUREC'[Sens] IN { 0 })
So it means the sum of the debit minus the sum of the credit

I know this is not the best way but I didn't know how to do..
Now, he asked me to calcule the average WCR.
To calculate that he sums the balance at the end of january, the balance at the end of february (so the balance from 1st of Janvuary to end of March) and the balance at the end of March (from the 1st of January to end of March) then divide by 3 to find the average WCR for March. I have a Date segment from the Calendar table. So he could choose in the segment the first quarter or with also April and May, and so on.
I really don't know how to do that as the number of month can vary..

Thank you!!

Need Help

2 Replies

  • 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...