Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi Everyone,
So I am trying to write a DAX Command to show MoM% Change for several metros I am Analyzing. The core calculation is the ((employment for the current metro)/(The employment for the month prior for the corresponding metro)-1). I am far more proficient in Excel and have used sumifs to solve this calculation, but I need help writing the formula for power BI. Because January's dates do not have a value before, I want to return a blank value.
The Table name is 'Employment Analysis', see sample table below:
Here is the formula I tried to write:
Solved! Go to Solution.
Hi @HCC ,
I think you can try this code to create a calculated column.
MoM% Change =
VAR _PerviousDate =
CALCULATE (
SUM ( 'Employment Analysis'[Employment] ),
FILTER (
ALLEXCEPT ( 'Employment Analysis', 'Employment Analysis'[Metro] ),
'Employment Analysis'[Date]
= EOMONTH ( EARLIER ( 'Employment Analysis'[Date] ), -2 ) + 1
)
)
VAR _CurrentDate =
CALCULATE ( SUM ( 'Employment Analysis'[Employment] ) )
VAR _Percentage =
DIVIDE ( _CurrentDate, _PerviousDate ) - 1
RETURN
IF ( _PerviousDate = BLANK (), BLANK (), _Percentage )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @HCC ,
I think you can try this code to create a calculated column.
MoM% Change =
VAR _PerviousDate =
CALCULATE (
SUM ( 'Employment Analysis'[Employment] ),
FILTER (
ALLEXCEPT ( 'Employment Analysis', 'Employment Analysis'[Metro] ),
'Employment Analysis'[Date]
= EOMONTH ( EARLIER ( 'Employment Analysis'[Date] ), -2 ) + 1
)
)
VAR _CurrentDate =
CALCULATE ( SUM ( 'Employment Analysis'[Employment] ) )
VAR _Percentage =
DIVIDE ( _CurrentDate, _PerviousDate ) - 1
RETURN
IF ( _PerviousDate = BLANK (), BLANK (), _Percentage )
Result is as below.
Best Regards,
Rico Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hi @HCC
try like:
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
20 | |
18 | |
18 | |
17 |
User | Count |
---|---|
37 | |
22 | |
18 | |
18 | |
13 |