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.
Hello all,
need to create a measure that would return 12 month running total for each month like in screenshot below.
Solved! Go to Solution.
Hi @Sam02
Create a measure
Measure =
CALCULATE (
SUM ( 'Fact'[Value] ),
FILTER (
ALLSELECTED ( 'Fact' ),
'Fact'[related date] <= MAX ( 'Fact'[related date] )
&& DATEDIFF ( 'Fact'[related date], MAX ( 'Fact'[related date] ), MONTH ) <= 12
)
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
There is a 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...
Thanks for your reply Greg, the quick measure did not worked and I created another one my self using different option both gave me same incorrect result.
following measure gives me Running total for continious period of time, Can some one help me to modify it for getting running total upto last 12 months only?
Hi @Sam02
Create a measure
Measure =
CALCULATE (
SUM ( 'Fact'[Value] ),
FILTER (
ALLSELECTED ( 'Fact' ),
'Fact'[related date] <= MAX ( 'Fact'[related date] )
&& DATEDIFF ( 'Fact'[related date], MAX ( 'Fact'[related date] ), MONTH ) <= 12
)
)
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Meggie, I was wondering if it is possible so the values for 2019 only and mabke it blank to others years in the past?
I need to get one line chart start at 2019 like in Screenshot below.
TIA,
Sam
Hi @Sam02
How about this?
Measure =
IF(YEAR(MAX('Fact'[related date]))=YEAR(Today()),
CALCULATE (
SUM ( 'Fact'[Value] ),
FILTER (
ALLSELECTED ( 'Fact' ),
'Fact'[related date] <= MAX ( 'Fact'[related date] )
&& DATEDIFF ( 'Fact'[related date], MAX ( 'Fact'[related date] ), MONTH ) <= 12
)
))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks a lot Maggie! That worked fine.
Link to sample pbix can be found here:
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 |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |