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 have a dax measure that should return me the sum of 2 highest volume in consecutive months.
The table as example is the following:
As seen above, the dax should first the max demand (March in this case) and its predecessor (February).
First dax I have is the one for the max demand:
VAR _A =
MAXX(
VALUES(Query1[Period[Month 445]]]),
Query1[Rolling with % produced by plant]
)
It returns correctly:
Then I calculate the maximum month (which should be March in this example):
Validation Max Month =
VAR _B =
CALCULATE (
MAX ( Query1[Period[Month 445]]] ),
FILTER ('Query1', [Rolling with % produced by plant] = MAXX(VALUES(Query1[Period[Month 445]]]),Query1[Rolling with % produced by plant])))
return _B
Is returning December which is incorrect (THIS IS THE DAX THAT NEEDS TO BE FIXED):
Then I calculate the predecessor month:
VAR _b =
CALCULATE (
MAX ( Query1[Period[Month 445]]] ),
FILTER ('Query1', [Rolling with % produced by plant] = MAXX(VALUES(Query1[Period[Month 445]]]),Query1[Rolling with % produced by plant])))
VAR _D =
DATE ( YEAR ( _B ), MONTH ( _B )-1, DAY ( _B ) )
RETURN _D
In this case returns November, which is incorrect (should be february)
The following dax is the total year demand:
Finally I calculate the 2 consecutive months:
2 Month Max demand =
Var _a =
MAXX(
VALUES(Query1[Period[Month 445]]]),
Query1[Rolling with % produced by plant]
)
VAR _b =
CALCULATE (
MAX ( Query1[Period[Month 445]]] ),
FILTER ('Query1', [Rolling with % produced by plant] = MAXX(VALUES(Query1[Period[Month 445]]]),Query1[Rolling with % produced by plant])))
Var _c = [Rolling with % produced by plant]
VAR _D =
DATE ( YEAR ( _B ), MONTH ( _B )-1, DAY ( _B ) )
RETURN
_a + CALCULATE ( [Rolling with % produced by plant] , Query1[Period[Month 445]]] = _D )
As seen the result it return is not correct, the dax above is summing March (3.6MM) and November (2.4)
It should be march (3.6) and feb (2.6).
How can I solve it?
Solved! Go to Solution.
Hi,
Thank you for your message.
Please check the below picture and the attached pbix file.
The below measure is one of many ways to achieve it.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure if I understood your question correctly, but please chech the below picture and the attached pbix file.
Expected result measure: =
VAR _topvalue =
CALCULATE (
SUM ( Data[KO Volume] ),
KEEPFILTERS (
WINDOW (
1,
ABS,
1,
ABS,
ALL ( Data[Month] ),
ORDERBY ( CALCULATE ( SUM ( Data[KO Volume] ) ), DESC )
)
)
)
VAR _prevmonthend =
EOMONTH (
FILTER (
ALL ( Data[Month] ),
CALCULATE ( SUM ( Data[KO Volume] ) ) = _topvalue
),
-1
)
VAR _prevmonthvalue =
CALCULATE (
SUM ( Data[KO Volume] ),
EOMONTH ( Data[Month], 0 ) = _prevmonthend
)
RETURN
_topvalue + _prevmonthvalue
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi @Jihwan_Kim
Sorry if I wasn't clear. Let me put it easier.
How can I return in a dax measure the month whose got the highest volume in any month?
In this case the highest volume is march, therefore the dax should return me March:
I already have calculated the maximum volume for march:
How could I get the march month with dax?
Thanks.
Hi,
Thank you for your message.
Please check the below picture and the attached pbix file.
The below measure is one of many ways to achieve it.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |