Montly project WIP balance in chart

[bvdm1980]

Hi, i have some question related to getting monthly values based on 2 different summed values. 

So i have project administrations. All data and tables in power bi.
For our WIP within our organisation, we use a simplified balance as in total invoiced to customer -/- total costs booked (= hours booked and invoices received).

These data are in 2 tables. One is ofcourse a customer detail table (relevant columns are book date, invoiced amount, project #) and the other data is the cost details (relevant are project #, book date, total costprice). Both are connected to my standard Calender table on the book date column. 

I can get the current total WIP amount ofcourse by 

WIP = sum (cust_detail [invoiced]) - sum(cost detail [total costprice])

But i would like to get a line chart, or bar chart (doesn't really matter for me) with the result of this formula, but then per month. 
If i make a chart and put in YYYYMM (from calender table) with the WIP measure result, it doesn't show correct data. 

And i don't really get it done or not sure how to use calculate for this. 
Thanks for the help in advance !

[v-rongtiep-msft · Accepted solution]

Hi @bvdm1980 ,

Please ensure that the relationships between the tables (Customer Details, Cost Details and Calendar) have been set up correctly, preferably unidirectional, to avoid any filter context issues.

Then modify the measure.

Monthly WIP Balance = 
CALCULATE(
    SUM(cust_detail[invoiced]) - SUM(cost_detail[total costprice]),
    ALLEXCEPT(Calendar, Calendar[YYYYMM])
)

 Create and manage relationships in Power BI Desktop - Power BI | Microsoft Learn

How to Get Your Question Answered Quickly - Microsoft Fabric Community

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[bvdm1980]

Thanks @v-rongtiep-msft , it seems to work! 

I don't really get the logic of ALLEXCEPT in this formula, as in my logical thinking this would filter some data from the table, but it shows all data but then per month. Which was ofcourse what i wanted : )