Cumulative value that changes with date slicer plotted on visual alongside monthly value

[james44452]

Hi,

I need to produce a visual that has the cumulative value (carbon emitted in the month) as a line and the monthly value plotted as a bar chart. I can't get the cumulative value right as it either just produces the monthly value or it doesn't calculate cumulatively from when the date slice date, it calculates cummatively from the start of the dataset.

The two measures I have created are as follows, with the first showing as the cumulative value from the beginning of the dataset when plotted:

cumulative_kinect_kg_CO2e =

VAR MinDate = MIN('calendar'[Date])

VAR MaxDate = MAX('calendar'[Date])

VAR DateList = 1

RETURN

CALCULATE(

    SUM(kinect_data[kg_CO2e]),

    FILTER(

        ALL('calendar'[Date]),

        'calendar'[Date] <= LOOKUPVALUE('calendar'[Date], 'calendar'[Date], MaxDate) &&

        'calendar'[Date] >= LOOKUPVALUE('calendar'[Date], 'calendar'[Date], MinDate)

    )

)

This one produces the monthly value when plotted:

cumulative_kinect_2 =

VAR MaxDate = MAX('calendar'[Date])

VAR DateList = 1

RETURN

CALCULATE(

    SUM(kinect_data[kg_CO2e]),

    FILTER(

        ALL('calendar'[Date]),

        'calendar'[Date] <= LOOKUPVALUE('calendar'[Date], 'calendar'[Date], MaxDate) &&

        'calendar'[Date] >= [start_date])

    )

I also need to filter it on another drop down list but can't get passed this at the minute!

Any help would be hugely appreciated.

James

[james44452 · Accepted solution]

Hi, thanks for getting back to me.

I managed to sort out the issue with the help of someone at work but I'll post more details below in case it's of use to anyone else.

The issue was that I had the data below and wanted to produce the graph below that, with the output varying depending on the date in the slicer.

Calendar data excerpt:

Date	calendar_month_number	calendar_year	financial_year	fy_name
01/04/2020	4	2020	2021	FY21
02/04/2020	4	2020	2021	FY21
03/04/2020	4	2020	2021	FY21
04/04/2020	4	2020	2021	FY21
05/04/2020	4	2020	2021	FY21
06/04/2020	4	2020	2021	FY21
07/04/2020	4	2020	2021	FY21
08/04/2020	4	2020	2021	FY21

Fuel data excerpt:

project_name	delivery_date	kg_CO2e
Project A	April 2020	2857.87852
Project A	May 2020	2474.43729
Project B	May 2020	1348.94073
Project A	June 2020	1462.0421
Project A	October 2020	5517.14
Project C	July 2020	6857.80502
Project C	May 2020	1439.97354

Example graph:

I solved this by linking 'delivery_date' in the second table to 'Date' but making the relationship inactive. I then created 3 measures as follows:

Sum of site fuel CO2e = SUM(fuel_data[kg_CO2e])

Sum of site fuel CO2e by date =

    CALCULATE([Sum of site fuel CO2e],

    USERELATIONSHIP('calendar'[Date], fuel_data[delivery_date])

)

Sum of site fuel CO2e by date running total in Date =

CALCULATE(

    [Sum of site fuel CO2e by date],

    FILTER(

        ALLSELECTED('calendar'[Date]),

        ISONORAFTER('calendar'[Date], MAX('calendar'[Date]), DESC)

    )

)

'Sum of site fuel CO2e by date' gave me the monthly data for the bars and 'Sum of site fuel CO2e by date running total in Date' gave me the cumulative value.

The trick was making the relationship between the date fields inactive and only using it when needed via the USERELATIONSHIP function.

James

[Anonymous]

Hi @james44452,

Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng