Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi Everyone,
I create two measures:
1. SUM_QTY:
(1) Goal: To sum all QTY in the data source
(2) DAX: SUM(Sheet1[QTY])
2. SUM_QTY_Last12M:
(1) Goal: To sum the QTY in the Last 12 Months
(2) DAX: CALCULATE([SUM_QTY], DATESINPERIOD('Dim_MTH'[MTH], DATE(YEAR(NOW()), MONTH(NOW()), 01), -1, YEAR))
The first one is working well, but the last one is weird.
Why SUM_QTY_Last12M always shows 431? Although 431 is the correct anwser...
Month | SUM_QTY | SUM_QTY_Last12M | What I Expect |
2021-01-01 | 98 | 431 | |
2021-02-01 | 4 | 431 | |
2021-03-01 | 33 | 431 | 33 |
2021-04-01 | 69 | 431 | 69 |
2021-05-01 | 81 | 431 | 81 |
2021-06-01 | 33 | 431 | 33 |
2021-07-01 | 6 | 431 | 6 |
2021-08-01 | 51 | 431 | 51 |
2021-09-01 | 33 | 431 | 33 |
2021-10-01 | 9 | 431 | 9 |
2021-11-01 | 21 | 431 | 21 |
2021-12-01 | 33 | 431 | 33 |
2022-01-01 | 6 | 431 | 6 |
2022-02-01 | 51 | 431 | 51 |
2022-03-01 | 38 | 431 | 38 |
Total | 566 | 431 | 431 |
Solved! Go to Solution.
Hi @Vanchy_Liao
Try this:
SUM_QTY_Last12 =
VAR _A =
CALCULATE(
[SUM_QTY],
DATESINPERIOD( Sheet1[Month], DATE( 2022, 3, 1 ), -12, MONTH )
)
VAR _B =
CALCULATE(
[SUM_QTY],
FILTER(
Sheet1,
Sheet1[Month]
IN DATESINPERIOD( Sheet1[Month], DATE( 2022, 3, 1 ), -12, MONTH )
)
)
RETURN
IF( ISFILTERED( Sheet1[Month] ), _B, _A )
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi @Vanchy_Liao
Try this:
SUM_QTY_Last12 =
VAR _A =
CALCULATE(
[SUM_QTY],
DATESINPERIOD( Sheet1[Month], DATE( 2022, 3, 1 ), -12, MONTH )
)
VAR _B =
CALCULATE(
[SUM_QTY],
FILTER(
Sheet1,
Sheet1[Month]
IN DATESINPERIOD( Sheet1[Month], DATE( 2022, 3, 1 ), -12, MONTH )
)
)
RETURN
IF( ISFILTERED( Sheet1[Month] ), _B, _A )
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
@Vanchy_Liao , to me last column seem same as the first one
for rolling
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
You can change no of months as per need
Abstract Thesis Part 60: Rolling Months Formula: https://youtu.be/GS5O4G81fww
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
86 | |
85 | |
68 | |
67 | |
63 |
User | Count |
---|---|
210 | |
118 | |
116 | |
81 | |
74 |