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.
for example I have a table
name | month | Amount | |
A | 2022-2 | 22 | |
A | 2022-3 | 30 | |
A | 2022-5 | 22 | |
A | 2022-7 | 30 | |
A | 2022-9 | 40 | |
A | 2022-10 | 18 | |
B | 2022-3 | 40 | |
B | 2022-4 | 12 | |
B | 2022-6 | 40 | |
B | 2022-7 | 22 | |
B | 2022-8 | 66 | |
B | 2022-10 | 45 |
I want to generate a new table to calculate like this(today is Nov,so the last 6 month should be from 2022-5 to 2022-10)
Name | last 6 month amount |
A | 110 |
B | 173 |
And the data should be refreshed as time goes by.
Can someone help?
Thanks.
Solved! Go to Solution.
Hi @yanzhao_LBS
Try the following code to create your new table:
Table 2 =
VAR _current_Date =
TODAY ()
VAR _End_Of_Last_Month =
EOMONTH ( _current_Date, -1 )
VAR _Start_of_5_monthago =
EOMONTH ( _End_Of_Last_Month, -6 )
RETURN
SUMMARIZE (
FILTER (
'Table',
'Table'[month] <= _End_Of_Last_Month
&& 'Table'[month] > _Start_of_5_monthago
),
[name],
"last 6 month amount", SUM ( 'Table'[Amount] )
)
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Hi @yanzhao_LBS
Try the following code to create your new table:
Table 2 =
VAR _current_Date =
TODAY ()
VAR _End_Of_Last_Month =
EOMONTH ( _current_Date, -1 )
VAR _Start_of_5_monthago =
EOMONTH ( _End_Of_Last_Month, -6 )
RETURN
SUMMARIZE (
FILTER (
'Table',
'Table'[month] <= _End_Of_Last_Month
&& 'Table'[month] > _Start_of_5_monthago
),
[name],
"last 6 month amount", SUM ( 'Table'[Amount] )
)
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
Thanks for your help,it works perfectly
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 |
---|---|
114 | |
100 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |