Help needed to calculate 'running total' and monthly totals

[scott_m]

Hi all. I was hoping someone might be able to help me with the required table structure/fields, relationships and DAX formulae needed in order to create a combo chart in Power BI that displays:

• the running total of open cases as at the end of month per month (as a line)
• the number of cases that had their opening occur at any point during a month per month (as a stacked column)
• the number of cases that had their closure occur at any point during a month per month (as a stacked column)
• the number of cases that were open at any point during a month per month (as a stacked column)

Please refer to the chart in the CASES – Calcs tab of the linked CASES.xlsx, which illustrates what I require.

The CASES and CASES_WITH_DUPLICATES tabs show two different tables I’ve used to try to calculate what I require.

• CASES has a unique case per row, which contains the case’s current status in CASE_STATUS, its opened date in DATE_CASE_RECEIVED, and its closed date (only if a case is currently closed) in DATE_CASE_CLOSED.

• DATE_CASE_CLOSED_NO_NULL is used to replace null values with 31/12/9999 in order to undertake calculations for cases that are currently open.

• EFFECTIVE_DATE is used to replace null values with the current date, which may better assist with undertaking calculations for cases that are currently open?

• CASES_WITH_DUPLICATES has two rows for each case if a case is currently closed – one row for when the case was opened, and a second row for when the case was closed. This allows for a simple count column to add 1 when a case is opened else subtract 1 when a case is closed.

• CASE_STATUS_DATE then acts as the date at which the count takes effect, where a case’s open row uses the DATE_CASE_RECEIVED and its closed row used the DATE_CASE_CLOSED.

• CASE_STATUS_DATE_CURRENTLY_OPEN_ADJ is the same as CASE_STATUS_DATE, however if a case is currently open (i.e. it has no corresponding closed row) then the date will instead be the current date, which may better assist with undertaking calculations for cases that are currently open?

I also have a separate date table as per the DATE tab which would be used for the date relationship.

The combo chart will need to be able to handle having its domain changed based on a date filter that users can choose in order to report on whatever date range they require, and the full table I’m using has other fields which will be used to splice the numbers further in other visualisations.

I’ve experimented using different permutations of formulae which include functions such as SUM, SUMX, FILTER, COUNTROWS, MIN, MAX, VALUES, ISONORAFTER, ALL, ALLSELECTED, etc. but I’m having no luck whatsoever.

Any help would be greatly appreciated, thank you!

[scott_m]

@amitchandak this worked for the first three calculations, thank you so much!

However, the Duplicate Active calculation didn't result in the required values. What I've used instead to calculate this is:

Managed Cases =

VAR Cases_Open_At_Prev_EOM =

    CALCULATE(

        COUNTX(

            FILTER(

                'CASES',

                'CASES'[DATE_CASE_RECEIVED] <= EOMONTH(MAX('DATE'[FULL_DATE]), -1) &&

                (ISBLANK('CASES'[DATE_CASE_CLOSED]) || 'CASES'[DATE_CASE_CLOSED] > EOMONTH(MAX('DATE'[FULL_DATE]), -1))

            ),

            'CASES'[CASE_NUMBER]

        )

    )

VAR Cases_Opened_During_Month =

    CALCULATE(

        COUNT('CASES'[CASE_NUMBER]),

        USERELATIONSHIP('CASES'[DATE_CASE_RECEIVED], 'DATE'[FULL_DATE])

    )

RETURN

    Cases_Open_At_Prev_EOM + Cases_Opened_During_Month

Thanks again!

[amitchandak · Accepted solution]

@scott_m , Create a date table and create inactive joins with all your dates. Do not create active joins. and then use measure like below and use along with date table

Open Cases = CALCULATE(COUNT(Cases[CASE_NUMBER]),USERELATIONSHIP(Cases[DATE_CASE_RECEIVED],'Date'[Date]) )

Closes Cases = CALCULATE(COUNT(Cases[CASE_NUMBER]),USERELATIONSHIP(Cases[DATE_CASE_CLOSED],'Date'[Date]),not(ISBLANK(Cases[DATE_CASE_CLOSED])))

Current Cases = CALCULATE(COUNTx(FILTER(Cases,Cases[DATE_CASE_RECEIVED]<=max('Date'[Date]) && (ISBLANK(Cases[DATE_CASE_CLOSED]) || Cases[DATE_CASE_CLOSED]>max('Date'[Date]))),(Cases[CASE_NUMBER])))

Duplicate Active = CALCULATE(COUNT(Cases[CASE_NUMBER]),FILTER(Cases,Cases[DATE_CASE_RECEIVED]<=max('Date'[Date]) )) - CALCULATE(COUNT(Cases[DATE_CASE_CLOSED]),FILTER(Cases,Cases[DATE_CASE_RECEIVED]<=max('Date'[Date]) ))