Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 🤠
Solved! Go to 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
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
@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])
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 |
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 |
@pajero What are you totaling?
@pajero Maybe:
Measure =
VAR __Date = MAX('Table'[handover date])
VAR __Table = FILTER(ALLSELECTED('Table'),[handover date] <= __Date)
RETURN
COUNTROWS(__Table,[Value])
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |