Forum Discussion

james44452's avatar
james44452
Frequent Visitor
3 years ago
Solved

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

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

Dashboards
Data Modeling
  • james44452's avatar
    james44452
    3 years ago

    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:

    Datecalendar_month_numbercalendar_yearfinancial_yearfy_name
    01/04/2020 420202021FY21
    02/04/2020420202021FY21
    03/04/2020 420202021FY21
    04/04/2020 420202021FY21
    05/04/2020 420202021FY21
    06/04/2020 420202021FY21
    07/04/2020 420202021FY21
    08/04/2020 420202021FY21

     

    Fuel data excerpt:

    project_namedelivery_datekg_CO2e
    Project AApril 20202857.87852
    Project AMay 20202474.43729
    Project BMay 20201348.94073
    Project AJune 20201462.0421
    Project AOctober 20205517.14
    Project CJuly 20206857.80502
    Project CMay 20201439.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

     

2 Replies

  • james44452's avatar
    james44452
    Frequent Visitor
    3 years ago

    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:

    Datecalendar_month_numbercalendar_yearfinancial_yearfy_name
    01/04/2020 420202021FY21
    02/04/2020420202021FY21
    03/04/2020 420202021FY21
    04/04/2020 420202021FY21
    05/04/2020 420202021FY21
    06/04/2020 420202021FY21
    07/04/2020 420202021FY21
    08/04/2020 420202021FY21

     

    Fuel data excerpt:

    project_namedelivery_datekg_CO2e
    Project AApril 20202857.87852
    Project AMay 20202474.43729
    Project BMay 20201348.94073
    Project AJune 20201462.0421
    Project AOctober 20205517.14
    Project CJuly 20206857.80502
    Project CMay 20201439.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