Help with DAX measure - Calculate accumulated deficit value

[Rai_Lomarques]

Hello colleagues, can anyone help me build a DAX measure?

Below I list all the tables and relationships in my data model:

Sales fact table called 'fSales containing the fields Date, Product_id, Customer_id, Salesperson_id, Wallet_id, Quantity, Sales_Value;

Goals fact table called 'fGoals' containing the fields Date, Product_id, Salesperson_id, Wallet_id, Quantity, Goal_Value;

Date dimension table called 'dCalendar' containing the fields Date, Month_Name, Month_Number, Year, Year/Month in the format "MM/YYYY", Month_Index in the format "YYYYMM". This table is related to 'fVendas' and 'fGoals' by the Date field;

I need a DAX measure that calculates the Target Gap, that is, the deficit, in an accumulated way, accumulating only the negative values.

Example:
Jan/2024 had a deficit of -100,000
Feb/2024 had a deficit of -50,000
Mar/2024 had a deficit of -20,000
Apr/2024 had a surplus of +50,000
May/2024 had a deficit of -5,000

From January to March the accumulated deficit was -170,000
In April the surplus reduced the deficit by 50,000 resulting in an accumulated deficit of -120,000
In May the deficit accumulated to -125,000

See below an image of the expected result

[v-csrikanth]

Hi @Rai_Lomarques 

We haven't heard from you since last response and just wanted to check whether the solution provided has worked for you. If yes, please Accept as Solution to help others benefit in the community.

If the above information is helpful, please give us Kudos and mark the response as Accepted as solution.
Best Regards,
Community Support Team _ C Srikanth.

[v-csrikanth]

Hi @Rai_Lomarques 
It's been a while since I heard back from you and I wanted to follow up. Have you had a chance to try the solutions that have been offered?
If the issue has been resolved, can you mark the post as resolved? If you're still experiencing challenges, please feel free to let us know and we'll be happy to continue to help!
Looking forward to your reply!

Best Regards,
Cheri Srikanth

[v-csrikanth]

Hi @Rai_Lomarques 
I wanted to follow up since I haven't heard from you in a while. Have you had a chance to try the suggested solutions?
If your issue is resolved, please consider marking the post as solved. However, if you're still facing challenges, feel free to share the details, and we'll be happy to assist you further.
Looking forward to your response!

[v-csrikanth]

Hi @Rai_Lomarques 
Please check the updated DAX for your issue.

Accumulated Deficit =

CALCULATE(

SUMX(

FILTER(

ALL('dCalendar'[Date]),

'dCalendar'[Date] <= MAX('dCalendar'[Date])

),

VAR CurrentDate = 'dCalendar'[Date]

VAR Deficit =

CALCULATE(

SUM('fGoals'[Goal_Value]) - SUM('fSales'[Sales_Value]),

'dCalendar'[Date] = CurrentDate,

ALLEXCEPT('fGoals', 'fGoals'[Product_id], 'fGoals'[Salesperson_id], 'fGoals'[Wallet_id]),

ALLEXCEPT('fSales', 'fSales'[Product_id], 'fSales'[Salesperson_id], 'fSales'[Wallet_id])

)

RETURN

IF(Deficit < 0, Deficit, 0)

),

ALL('dCalendar')

)

If the above information helps you, please give us a Kudos and marked the Accept as a solution.
Best Regards,
Community Support Team _ C Srikanth.

[Greg_Deckler]

@Rai_Lomarques Well, if you are counting the positive values then you aren't accumulating just the negative values only. Seems like a basic running total. https://community.fabric.microsoft.com/t5/Quick-Measures-Gallery/Better-Running-Total/m-p/2755666#M8...

[Rai_Lomarques]

This is what I need, to accumulate, but it does not apply correctly to my case, as it is considering all previous values, I need it to consider only negative values ​​(deficit) and return 0 (zero) for surplus cases, as illustrated in the table in the image.