Chart with running total that can update based on slicer

[tbroman]

Hello! I am looking for some help on how to build a visual that is proving tricky. What I have now works, but it uses a calculated column, and I want to update it to work with a slicer.

I'm reporting on a case management system, and the visual I need is a chart of, at the end of each of the last 12 months, how many open cases there were at the end of that month. The way I'm doing this currently is: I have a 'Years x Months' table that contains one line for each (Month, Year) combination in the covered range, with a "Last Day" date column. All of this is defined at the PowerQuery level. Then, I created a calculated column as follows:

OpenCases = calculate(DISTINCTCOUNTNONBLANK('Master Combined Table with Duplicates'[Participant ID],

filter('Master Combined Table with Duplicates',

and(or(isblank('Master Combined Table with duplicates'[Case End Date]),

'Master Combined Table with duplicates'[Case End Date].[Date]>'Years x Months'[Last Day]),

and(not(isblank('Master Combined Table with duplicates'[Case Begin Date])),

'Master Combined Table with Duplicates'[Case Begin Date].[Date]<='Years x Months'[Last Day])))))

Basically, how many cases had a Case Begin Date in the past and a Case End Date that was blank or in the future (relative to that day)?

Then, to display the last 12 months in a chart, I used the following measure:

OpenCasesLast12Mos = calculate(sum([OpenCases]),filter('Years x Months',and('Years x Months'[Last Day]<[LastDayActiveRange],'Years x Months'[Last Day]>=[LastYearMonthFirstDay])))

where

LastDayActiveRange = EOMonth([FirstDayActiveRange],0)

FirstDayActiveRange = date(SELECTEDVALUE(Years[Year]),SELECTEDVALUE(Months[MonthNum],1)

LastYearMonthFirstDay = date(SELECTEDVALUE(Years[Year])-1,SELECTEDVALUE(Months[MonthNum]),1)

These measures are defined alongside a date table called 'Calendar' with values from 1/1/2022 to 1/1/2024. 

The 'Years' table contains a single column called [Year], with rows 2022 and 2023. The 'Months' table contains 12 rows, one per month. There is a 'Years' slicer and a 'Months' slicer to force a selected value.

The chart is a basic Stacked Column Chart that has [Last Day] on the x-axis and OpenCasesLast12Mos on the y-axis.

But now I want to add a slicer! The 'Master Combined Table with duplicates' has a "Program Title" column, and I'd like to be able to filter the open cases by program. There are four programs, and I'd like to have an option that works if none of the options or more than one of the options are selected, as well as just one option. Unfortunately, using the calculated column seems to mean that I can't use a slicer, and I haven't been able to figure out how to efficiently make this work. 

Here's a link to my data tables: https://docs.google.com/spreadsheets/d/1ocfljxalY8VcWMp3hO3V7gsydMMgjhNA/edit?usp=sharing&ouid=11366...

Any help would be appreciated! It seems like the big thing that makes this hard is wanting to do 12 instances of relative date math on the entire table.

[Ashish_Mathur]

Hi,

In another worksheet of that workbook, show the expected result very clearly.  If possible, show the process usch as Pivot Tables/lookups that you would use to arrive at the result.  Share the download link of that revised file.

[tbroman]

Hello, thank you for your response! I have added some info to the spreadsheet and updated at the same link. I added four calculated columns to the 'Years x Months' sheet that explicitly calculate the four OpenCases subtotals by program each month. (This is not a workable solution for my PowerBI file because I don't want to hard-code a new column for each program, in case the programs change.) Then I made a PivotChart on a new sheet--but what the chart shows isn't exactly what I'm looking for. Imagine a stacked column chart instead of a clustered column chart, and have it just display the total instead of the component subtotals. Then, if you filter the PivotChart by changing which of [A/B/C/D] are included, you'd see the total change. That's the behavior I want when I toggle values on the slicer in Power BI.

[Ashish_Mathur]

You are welcome.  PBI file attached.

Hope this helps.

[amitchandak]

@tbroman , Create a date table join with date and then try like

Cumm Sales = CALCULATE(SUM(sum([OpenCases]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Sales = CALCULATE(SUM(sum([OpenCases]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Based on Date = CALCULATE(sum([OpenCases]), Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))

Cumm Based on Date = CALCULATE(sum([OpenCases]), Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))

if open is based on two dates consider

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

Average of Rolling, Average of Snapshots: https://youtu.be/_pZRdLAJxxA

Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

[tbroman]

Thank you for your response! "Open" is indeed based on two dates, so I tried implementing your "Employees as on Date". I don't have it working right at the moment. I think one issue is that you have a clean Employee table with one line per employee. My count is technically not the number of open cases, it is the number of participants with open cases, and a participant may have more than one case in the table. So my current calculated-column method uses a DISTINCTCOUNT formula on the Participant ID column, and using a COUNTX loses that uniqueness. Is there a good way around that?