Forum Discussion
Cumulative value that changes with date slicer plotted on visual alongside monthly value
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
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: