subtotal each month cumulative

[pajero]

Hi all,

I am looking for a function that allows me to make a total in the table per column (month). Next month, the number from last month must be added.

Any tips?
tnx

ending up like this but without the manual work 🤠

[v-yangliu-msft · Accepted solution]

Hi  @pajero ,

Here are the steps you can follow：

1. Create calculated column.

Year_Month =
YEAR('Table'[handover date])&"-"&FORMAT('Table'[handover date],"mm")

Month =
MONTH('Table'[handover date])

2. In Power query. Add Column – Index Column – From 1.

3. Create measure.

Measure =
COUNTX(FILTER(ALL('Table'),
'Table'[loc_site_category]=MAX('Table'[loc_site_category])&&
'Table'[loc_site_type]=MAX('Table'[loc_site_type])&&'Table'[Year_Month]=MAX('Table'[Year_Month]))
,[loc_name])

Measure 2 =
IF(  MAX('Table'[Index])=MINX(FILTER(ALL('Table'),'Table'[loc_site_category]=MAX('Table'[loc_site_category])&&
'Table'[loc_site_type]=MAX('Table'[loc_site_type])&&'Table'[Year_Month]=MAX('Table'[Year_Month])),[Index]),[Measure],0)

Measure 3 =
SUMX(FILTER(ALLSELECTED('Table'),'Table'[loc_site_category]=MAX('Table'[loc_site_category])&&'Table'[loc_site_type]=MAX('Table'[loc_site_type])
&&'Table'[handover date]<=MAX('Table'[handover date])),[Measure 2])

4. Result:

If you need pbix, please click here.

subtotal each month cumulative.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Greg_Deckler]

@pajero Depends on your data, but the general form for a running total is something like:

Measure =
  VAR __Date = MAX('Table'[Date])
  VAR __Table = FILTER(ALLSELECTED('Table'),[Date] <= __Date)
RETURN
  SUMX(__Table,[Value])

[pajero]

@Greg_Deckler 

if you want to play this is more or less the data

loc_name	loc_site_category	loc_site_type	handover date
a	XXX uni	Indoor	19/04/2022
b	XXX uni	Indoor	19/04/2022
c	consolidated	Indoor	19/04/2022
d	XXX uni	Indoor	19/04/2022
e	AAA uni	Indoor	05/05/2022
f	consolidated	Macro	09/05/2022
g	AAA uni	Indoor	19/04/2022
h	AAA uni	Indoor	05/05/2022
i	AAA uni	Indoor	19/04/2022
j	AAA uni	Indoor	09/03/2022
k	AAA uni	Indoor	25/03/2022
l	AAA uni	Indoor	05/05/2022
m	AAA uni	Indoor	01/02/2022
n	AAA uni	Indoor	19/04/2022
o	AAA uni	Indoor	01/02/2022
p	AAA uni	Indoor	25/03/2022
q	AAA uni	Indoor	19/04/2022
r	AAA uni	Indoor	19/04/2022
s	AAA uni	Indoor	19/04/2022
t	AAA uni	Indoor	19/04/2022
u	AAA uni	Indoor	19/04/2022
v	consolidated	Indoor	18/03/2022
w	AAA uni	Indoor	19/04/2022
x	AAA uni	Indoor	19/04/2022
y	AAA uni	Indoor	05/05/2022
z	consolidated	Macro	09/05/2022
aa	consolidated	Macro	09/05/2022
bb	consolidated	Macro	09/05/2022
cc	consolidated	Macro	09/05/2022
dd	consolidated	Macro	09/05/2022
ee	consolidated	Macro	09/05/2022
ff	consolidated	Macro	09/05/2022
gg	consolidated	Macro	09/05/2022
hh	consolidated	Macro	09/05/2022
ii	consolidated	Macro	09/05/2022
jj	consolidated	Macro	09/05/2022
kk	consolidated	Macro	09/05/2022
ll	consolidated	Macro	09/05/2022
mm	consolidated	Macro	09/05/2022
nn	consolidated	Macro	09/05/2022
oo	consolidated	Macro	09/05/2022
pp	consolidated	Macro	09/05/2022
qq	AAA uni	Indoor	25/03/2022
rr	AAA uni	Indoor	25/03/2022
ss	consolidated	Macro	09/05/2022
tt	consolidated	Macro	09/05/2022
uu	consolidated	Macro	09/05/2022
vv	XXX uni	Macro	09/05/2022
ww	consolidated	Macro	09/05/2022
xx	consolidated	Macro	09/05/2022
yy	consolidated	Macro	09/05/2022
zz	consolidated	Macro	09/05/2022
aaa	consolidated	Macro	09/05/2022
bbb	consolidated	Macro	09/05/2022
ccc	AAA uni	Indoor	25/03/2022
ddd	consolidated	Macro	09/05/2022
eee	consolidated	Macro	09/05/2022
fff	consolidated	Macro	09/05/2022
ggg	consolidated	Macro	09/05/2022
hhh	consolidated	Macro	09/05/2022
iii	consolidated	Macro	09/05/2022
jjj	consolidated	Macro	09/05/2022
kkk	consolidated	Macro	09/05/2022
lll	consolidated	Macro	09/05/2022
mmm	consolidated	Macro	09/05/2022

[Greg_Deckler]

@pajero What are you totaling?

[pajero]

@Greg_Deckler a count of "LOC_name" based on the handoverdate

[Greg_Deckler]

@pajero Maybe:

Measure =
  VAR __Date = MAX('Table'[handover date])
  VAR __Table = FILTER(ALLSELECTED('Table'),[handover date] <= __Date)
RETURN
  COUNTROWS(__Table,[Value])