Running total issue

[Anonymous]

Hi all,

Please issue sample of my data set, My data is large data below i am showing sample

Client Name	Case created date	case no	type
a	22/08/2019	222	x
a	22/08/2019	223	x
a	21/08/2019	224	x
b	20/07/2019	225	y
b	19/07/2019	226	y
b	15/07/2019	227	y
b	14/07/2019	228	z
c	13/05/2019	223	z
c	16/05/2019	229	z
c	17/04/2019	230	z
c	13/03/2019	231	x
c	12/2/2019	232	x

I want to calcualte distinct count of client code based on cases id we received last 90 days by running total.  (last three months if we received cases from the client which means client is alive)

I want to represents the data like below to create visual

Total Alive clients
	July	Aug
X	16	15	( cases received in last three months)
Y	17	14
Z	18	12

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try to use following measure formula if it suitable for your requirement:

Measure =
VAR curr =
    MAX ( 'Table'[Case created date] )
RETURN
    CALCULATE (
        COUNTA ( 'Table'[case no] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [Case created date]
                >= DATE ( YEAR ( curr ), MONTH ( curr ) - 3, 1 )
                && [Case created date] <= curr
        ),
        VALUES ( 'Table'[Client Name] )
    )

Regards,
Xiaoxin Sheng

[Greg_Deckler]

Have you tried using the built-in Running Total Quick Measure? Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...