topic Re: Create Waterfall Chart with breakdown based on date Selected value in DAX Commands and Tips

Create Waterfall Chart with breakdown based on date Selected value

Fantmas — Sun, 28 Aug 2022 16:54:59 GMT

Hi all,

I am new in the PowerBi journey, I am trying to create a waterfall that display movement from based on a date value in a slicer,

in my measure I got the following error message and I am stuck to fix it, can you help me :

below the measure

FTE variance by MONTH = var selectedMonth = SELECTEDVALUE('Period Available'[Period]) var selectedOMTYPE = SELECTEDVALUE(DIMBRIDGE[OM TYPE (FTE BRIDGE)]) return SWITCH(selectedMonth, SELECTEDVALUE(DimDate[EndMonth]), SWITCH(selectedOMTYPE, "1. HIRING / REHIRING", [1. HIRING / REHIRING], "2. CHANGE IN THE CONTRACT TYPE", [2. CHANGE IN THE CONTRACT TYPE], "3. CHANGE IN ACTUAL WORK % (FTE CHANGE)", [3. CHANGE IN ACTUAL WORK % (FTE CHANGE)], "4. BACK FROM HOLD", [4. BACK FROM HOLD], "6. INTERIM IN", [6. INTERIM IN], "9. ONLY WS CHANGE IN", [9. ONLY WS CHANGE IN], "9. TECH UNEXPLAINED", [9. TECH UNEXPLAINED], "A. DEPARTURE", [A. DEPARTURE], "B. CHANGE IN THE CONTRACT TYPE", 0, "C. CHANGE IN ACTUAL WORK % (FTE CHANGE)", 0, "D. ON HOLD", 0, "F. INTERIM OUT", 0, "X. ONLY WS CHANGE IN", 0, "I. TECH UNEXPLAINED", 0, [FTE M] ), EOMONTH(SELECTEDVALUE(DimDate[EndMonth]),-1), SWITCH(selectedOMTYPE, "1. HIRING / REHIRING", 0, "2. CHANGE IN THE CONTRACT TYPE", 0, "3. CHANGE IN ACTUAL WORK % (FTE CHANGE)", 0, "4. BACK FROM HOLD", 0, "6. INTERIM IN", 0, "9. ONLY WS CHANGE IN", 0, "9. TECH UNEXPLAINED", 0, "A. DEPARTURE", -1*[A. DEPARTURE], "B. CHANGE IN THE CONTRACT TYPE", -1*[B. CHANGE IN THE CONTRACT TYPE], "C. CHANGE IN ACTUAL WORK % (FTE CHANGE)", -1*[C. CHANGE IN ACTUAL WORK % (FTE CHANGE)], "D. ON HOLD", -1*[D. ON HOLD], "F. INTERIM OUT", -1*[F. INTERIM OUT], "X. ONLY WS CHANGE IN", -1*[X. ONLY WS CHANGE OUT], "I. TECH UNEXPLAINED", -1*[I. TECH UNEXPLAINED], [FTE M-1]) )

Re: Create Waterfall Chart with breakdown based on date Selected value

amitchandak — Mon, 29 Aug 2022 04:20:17 GMT

Fantmas , Try like

FTE variance by MONTH =

var selectedMonth = SELECTEDVALUE('Period Available'[Period])
var selectedOMTYPE = SELECTEDVALUE(DIMBRIDGE[OM TYPE (FTE BRIDGE)])

return
SWITCH(True() ,
selectedMonth =SELECTEDVALUE(DimDate[EndMonth]),
SWITCH(selectedOMTYPE,
"1. HIRING / REHIRING", [1. HIRING / REHIRING],
"2. CHANGE IN THE CONTRACT TYPE", [2. CHANGE IN THE CONTRACT TYPE],
"3. CHANGE IN ACTUAL WORK % (FTE CHANGE)", [3. CHANGE IN ACTUAL WORK % (FTE CHANGE)],
"4. BACK FROM HOLD", [4. BACK FROM HOLD],
"6. INTERIM IN", [6. INTERIM IN],
"9. ONLY WS CHANGE IN", [9. ONLY WS CHANGE IN],
"9. TECH UNEXPLAINED", [9. TECH UNEXPLAINED],
"A. DEPARTURE", [A. DEPARTURE],
"B. CHANGE IN THE CONTRACT TYPE", 0,
"C. CHANGE IN ACTUAL WORK % (FTE CHANGE)", 0,
"D. ON HOLD", 0,
"F. INTERIM OUT", 0,
"X. ONLY WS CHANGE IN", 0,
"I. TECH UNEXPLAINED", 0,
[FTE M]
),
selectedMonth = EOMONTH(SELECTEDVALUE(DimDate[EndMonth]),-1),
SWITCH(selectedOMTYPE,
"1. HIRING / REHIRING", 0,
"2. CHANGE IN THE CONTRACT TYPE", 0,
"3. CHANGE IN ACTUAL WORK % (FTE CHANGE)", 0,
"4. BACK FROM HOLD", 0,
"6. INTERIM IN", 0,
"9. ONLY WS CHANGE IN", 0,
"9. TECH UNEXPLAINED", 0,
"A. DEPARTURE", -1*[A. DEPARTURE],
"B. CHANGE IN THE CONTRACT TYPE", -1*[B. CHANGE IN THE CONTRACT TYPE],
"C. CHANGE IN ACTUAL WORK % (FTE CHANGE)", -1*[C. CHANGE IN ACTUAL WORK % (FTE CHANGE)],
"D. ON HOLD", -1*[D. ON HOLD],
"F. INTERIM OUT", -1*[F. INTERIM OUT],
"X. ONLY WS CHANGE IN", -1*[X. ONLY WS CHANGE OUT],
"I. TECH UNEXPLAINED", -1*[I. TECH UNEXPLAINED],
[FTE M-1])
)

Re: Create Waterfall Chart with breakdown based on date Selected value

Fantmas — Mon, 29 Aug 2022 17:52:26 GMT

Hi Amit,

it seems working now, I have an additional question, is it possible to do a dynamic title for the category,

When the user select the date he want to see, the column title will change depending of the selected date ?

Thank a lot for your support

Regards

Re: Create Waterfall Chart with breakdown based on date Selected value

v-yadongf-msft — Wed, 31 Aug 2022 10:03:04 GMT

Hi Fantmas ,

Maybe you can use drill down function to do a dynamic title for the category.

When you drill down, the column title will change depending of the selected date.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Create Waterfall Chart with breakdown based on date Selected value

Fantmas — Thu, 01 Sep 2022 16:28:01 GMT

Hi,

Thank you for your feedback, it sems working now,

I amr requesting a last support from powerBi community , I Try get the sum of previous month value based on Slicer selection my code return me blank and I do not understand why :

M-1 = Calculate( [FTE MTD], REMOVEFILTERS( DimDate[Date] ), 'Fact FTE'[Period Available] = EOMONTH( SELECTEDVALUE(DimDate[Date]),-1))