topic Re: DAX formula in DAX Commands and Tips

DAX formula

Anonymous — Mon, 05 Aug 2024 07:25:46 GMT

I want to remove "demand.00" from values section. I tried INT and ROUND function to solve but nothing worked. If I'm changing it to integer then "3M MDS % change" is also changing to integer, but it should be in %.

Total Demand =

VAR LWK = CALCULATE(

SUM(Append1[Demand]),

Append1[WK] = "LWK",

Append1[Month-Year] >= MIN('Append1'[Month-Year]),

Append1[Month-Year] <= EDATE(MIN('Append1'[Month-Year]), 2))

VAR CWK = CALCULATE(

SUM(Append1[Demand]),

Append1[WK] = "CWK",

Append1[Month-Year] >= MIN('Append1'[Month-Year]),

Append1[Month-Year] <= EDATE(MIN('Append1'[Month-Year]), 2))

VAR Change = SWITCH(

TRUE(),

LWK = CWK, 0,

LWK = 0 && CWK <> 0, 100,

CWK = 0 && LWK <> 0, -100,

LWK <> 0, DIVIDE((LWK-CWK), LWK, 0) * 100 )

RETURN

IF(ISBLANK(MAX(Append1[Month-Year]))=FALSE(),

IF(DISTINCTCOUNT(Append1[Month-Year])>1,

Change,

SUM(Append1[Demand])

)

Re: DAX formula

bhanu_gautam — Mon, 05 Aug 2024 07:30:51 GMT

Anonymous , Try updating measure as

Total Demand =
VAR LWK = CALCULATE(
SUMX(Append1, TRUNC(Append1[Demand])),
Append1[WK] = "LWK",
Append1[Month-Year] >= MIN('Append1'[Month-Year]),
Append1[Month-Year] <= EDATE(MIN('Append1'[Month-Year]), 2))
VAR CWK = CALCULATE(
SUMX(Append1, TRUNC(Append1[Demand])),
Append1[WK] = "CWK",
Append1[Month-Year] >= MIN('Append1'[Month-Year]),
Append1[Month-Year] <= EDATE(MIN('Append1'[Month-Year]), 2))
VAR Change = SWITCH(
TRUE(),
LWK = CWK, 0,
LWK = 0 && CWK <> 0, 100,
CWK = 0 && LWK <> 0, -100,
LWK <> 0, DIVIDE((LWK-CWK), LWK, 0) * 100 )
RETURN
IF(ISBLANK(MAX(Append1[Month-Year]))=FALSE(),
IF(DISTINCTCOUNT(Append1[Month-Year])>1,
Change,
SUMX(Append1, TRUNC(Append1[Demand]))
)
)

Re: DAX formula

SachinNandanwar — Mon, 05 Aug 2024 07:51:29 GMT

Have you tried FORMAT function ?

FORMAT(YourValue, "0.0") & "%"

Re: DAX formula

Anonymous — Mon, 05 Aug 2024 08:12:43 GMT

Sorry but it is still showing same as above.

Re: DAX formula

Anonymous — Mon, 05 Aug 2024 08:23:14 GMT

Yes, I tried.

IF(ISBLANK(MAX(Append1[Month-Year]))=FALSE(),

IF(DISTINCTCOUNT(Append1[Month-Year])>1,

FORMAT(Change, "0.00") & "%",

FORMAT(SUM(Append1[Demand]), "")

)

)
and it worked.
Thankyou so much.

Re: DAX formula

SachinNandanwar — Mon, 05 Aug 2024 08:59:26 GMT

Great. Please mark my solution as solved.