Cumulative costs

[GKJARC]

Hi everyone,

I'm trying to visualize cumulative costs a vertical bar chart, by year-month on the X-axis and cumulative costs on the Y-axis.

My TableA has data of CO2 emissions per day, TableB has daily the prices of CO2 emissions per day. Both my TableA and TableB are related to a Calendar table by Date.

Non-cumulative costs I can manage. The CO2 emission allowances are purchased each month, so I'm using the average monthly price of CO2. The sum of CO2 emissions * the average of price works well since both are related to the Calandar table.

When I create a cumulative measure, it adds up the CO2 emissions cumulatively correctly.
The issue is that it also recalculates the price. For example:

January: 5 tons of CO2 * average CO2 price in January of 70 Euro = 350 Euro.

February: 5 tons of CO2 * average CO2 price in February of 60 Euro = 300 Euro.

So the cumulative CO2 emitted = 10 tons, and the cumulative cost should amount to 650 Euro (350 of January + 300 of February).

However for February my DAX recalculates all CO2 emitted with the price of both months, resulting in an incorrect cumulative total cost for 2024-02.
Sample pbix:
https://we.tl/t-jSY5m7tuGy

Any help would be appreciated, thanks!
 

[GKJARC]

@Ashish_Mathur and @TomMartens  thank you for your replies. I created a sample pbix to clarify the issue:
https://we.tl/t-jSY5m7tuGy

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[TomMartens]

Hey @GKJARC ,

please share the pbix via OneDrive, Google Drive, or Dropbox.

Regards,

Tom

[Ashish_Mathur]

Hi,

Drag Year and Month name from the Calendar table to the visual.  Assuming, you already have  Cost measure, one of these 2 measures should work?

Measure = SUMX(SUMMARIZE(Calendar,datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date]),calendar[month name],calendar[year],"A",[Cost]),[A])

Measure1 = calculate([Cost],datesbetween(calendar[date],minx(all(calendar),calendar[date]),max(calendar[date])))

If neither works, then share the download link of the PBI file.  Show the problem there very clearly.

[TomMartens]

Hey @GKJARC ,

please create a pbix containing sample data that still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix file to OneDrive, Google Drive, or Dropbox and share the link. if you are using a spreadsheet to create the sample data instead of the manual input method share the spreadsheet as well.

Regards,

Tom