Create Waterfall Chart with breakdown based on date Selected value

[Fantmas]

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])
            )

[Fantmas · Accepted solution]

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))

[amitchandak]

@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])
)

[Fantmas]

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

[v-yadongf-msft]

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.