Waterfall does not display as needed

[ryanli1982]

I created a measure and two extra tables by referring to some posts in this forum, but still cannot make the visual right.

Thank you for your help in advance!

New = new income every quarter

Monthly = monthly expense

OT = one-time expense

FY23/24 Q3 is the first time this ID has new income $116.3K, no monthly or one-time expense, so the beginning is zero, and making the FY23/24 Q4 beginnings $116.3K - $0 - $0 = $116.3K.

The issue happens in FY24/25 Q1. The -$7,942 monthly expense (-$8K in the visual) happened in Q1, and should be placed in between FY24/25 Q1 and FY24/25 Q2. However, it appears between FY23/24 Q4 and FY24/25 Q1, which is not what I expect. 

Raw Data Table:

fy_qtr_desc	ID	New	Monthly	OT	index	Starting	Ending	Accu_New	Accu_OT	Accu_Monthly
FY23/24Q3	C000004995	$116,345.9			95		$116,345.89	$116,345.9
FY23/24Q4	C000004995		$0.0	$128,306.1	96	$116,345.9	($11,960.1802)	$116,345.9		$0.0
FY24/25Q1	C000004995		$7,972.2	$0.0	97	($11,960.2)	($19,932.3488)	$116,345.9	$128,306.1	$7,972.2
FY24/25Q2	C000004995		$10,770.3	$0.0	98	($19,932.3)	($30,702.6469)	$116,345.9	$128,306.1	$18,742.5
FY24/25Q3	C000004995		$10,623.2	$0.0	99	($30,702.6)	($41,325.8674)	$116,345.9	$128,306.1	$29,365.7
FY24/25Q4	C000004995		$10,330.4	$0.0	100	($41,325.9)	($51,656.2886)	$116,345.9	$128,306.1	$39,696.1

two supporting tables:

breakdowncategory

My measure:

Waterfall_Qtr_End_Fiscal_Q_ID =
VAR _Starting =  CALCULATE(SUM(raw_data_table[Starting]))
VAR _New = CALCULATE(SUM(raw_data_table[New]))
VAR _OT = CALCULATE(SUM(raw_data_table[Accu_OT]))
VAR _monthly = CALCULATE(SUM(raw_data_table[Accu_Monthly]))
VAR _Ending = CALCULATE(SUM(raw_data_table[Ending]))
Return
  SWITCH (
    SELECTEDVALUE (Category[Category_Sort]),
    1, SWITCH (
        SELECTEDVALUE (Breakdown[Breakdown]),
        "New",-1*_New,
        "OT",-1*_OT,
        "monthly",-1*_monthly,
        _Starting
    ),
    2, SWITCH (
        SELECTEDVALUE (Breakdown[Breakdown]),
        "New", 0,
        "OT",0,
        "monthly",0,
        _Ending
    )
)

The only two filter I used are:

ID = C000004995

and

 

[v-pgoloju]

Hi @ryanli1982,

Thank you for reaching out to the Microsoft Fabric Forum Community. And also thanks to @burakkaragoz for prompt and helpful response.

Just checking in to see if the suggestions shared by @burakkaragoz  helped in resolving the issue.

If the response addressed your concern, please consider marking one as the Accepted Solution.

Feel free to reach out if you need further assistance or clarification.

Best regards,
Prasanna Kumar

[burakkaragoz · Accepted solution]

Hi @ryanli1982 ,

I see what you're trying to achieve and I think you're really close — just a small tweak might fix the issue.

From what I understand, the problem is that the monthly expense for FY24/25 Q1 is showing up before that quarter, instead of between Q1 and Q2 as expected. This usually happens when the logic in the measure doesn't align with how the visual interprets the data contextually.

Looking at your measure, you're using SELECTEDVALUE(Category[Category_Sort]) and Breakdown[Breakdown] to drive the SWITCH logic. But the issue might be that the quarter context isn't being handled explicitly — especially if the visual is aggregating or sorting based on something like Fiscal_Q_ID or a similar field.

Here’s a couple of things you might want to try:

1. Make sure your axis (x-axis) in the visual is sorted properly — ideally by a numeric index like Fiscal_Q_ID or a custom sort column. Sometimes visuals misplace values if the sort order isn't strict.

2. In your measure, try to explicitly bring in the quarter context using something like:

   VAR _CurrentQtr = SELECTEDVALUE('DateTable'[Fiscal_Q_ID])

   Then use that to filter your calculations more precisely. For example:

   VAR _monthly = CALCULATE(SUM(raw_data_table[Accu_Monthly]), 'DateTable'[Fiscal_Q_ID] = _CurrentQtr)

   This ensures that the value is tied to the correct quarter, not just floating based on the visual's grouping.

3. Also double-check if your Accu_Monthly column is cumulative or not. If it's cumulative, you might need to subtract the previous quarter’s value to get the delta for the current quarter.

Let me know if you want help rewriting the measure with these ideas — happy to help further.

If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

---

This response was supported by AI for translation and text editing.