Using a calculated collumn I want to calculate market value difference

[ImSh7m]

I want to calculate value difference 

only

 if there’s a flagged change, but the results are incorrect or blank

I’m trying to compare the "value " between two time steps (e.g. x+10 and x+09) per " ID."

For each " ID " and "time step", I want to calculate the difference in "value " between the current and previous time step only if the current "value " is flagged in my other calculated collumn "Value change status" as "Change"

My main table "Files "contains data over time, with key columns like YearQuarter (e.g., “2024-Q1”), SourceName (e.g., “_x+04, x+05, x+06....”), InvestmentID (non-unique, as each investment can have multiple positions), and Value ("value "). It also includes a calculated column ValueChangeStatus, which flags a row as "Change" if the total value for that investment has changed compared to the previous date(for creating this collumn i used almost the same logic used here in my new calculated column)

I’m trying to create a new calculated column that, for each row where ValueChangeStatus = "Change", calculates the difference in total Value for the same InvestmentID between the current and previous reporting dates. I defined the current and previous dates using variables CurrentDate and PreviousDate, based on the _x+NN suffix in SourceName. The goal is to return the rounded difference between ValueCurrent and ValuePrevious.

This iis the calculated collumn i created to calculate the difference in values which doesnt work :

VAR YearQuarter = Files[Quartal_Jahr]

VAR CurrentDate =
SWITCH(
TRUE(),
Files[Source.Name3] = YearQuarter & "_x+04", YearQuarter & "_x+04",
Files[Source.Name3] = YearQuarter & "_x+05", YearQuarter & "_x+05",
Files[Source.Name3] = YearQuarter & "_x+06", YearQuarter & "_x+06",
Files[Source.Name3] = YearQuarter & "_x+07", YearQuarter & "_x+07",
Files[Source.Name3] = YearQuarter & "_x+08", YearQuarter & "_x+08",
Files[Source.Name3] = YearQuarter & "_x+09", YearQuarter & "_x+09",
Files[Source.Name3] = YearQuarter & "_x+10", YearQuarter & "_x+10",
Files[Source.Name3] = YearQuarter & "_x+Final", YearQuarter & "_x+Final",
BLANK()
)

VAR PreviousDate =
SWITCH(
TRUE(),
Files[Source.Name3] = YearQuarter & "_x+Final", YearQuarter & "_x+10",
Files[Source.Name3] = YearQuarter & "_x+10", YearQuarter & "_x+09",
Files[Source.Name3] = YearQuarter & "_x+09", YearQuarter & "_x+08",
Files[Source.Name3] = YearQuarter & "_x+08", YearQuarter & "_x+07",
Files[Source.Name3] = YearQuarter & "_x+07", YearQuarter & "_x+06",
Files[Source.Name3] = YearQuarter & "_x+06", YearQuarter & "_x+05",
Files[Source.Name3] = YearQuarter & "_x+05", YearQuarter & "_x+04",
BLANK()
)

VAR ValueCurrent =
CALCULATE(
SUM(Files[VALUE_EUR]),
Files[Source.Name3] = CurrentDate &&
Files[ValueChangeStatus] = "Change"
)

VAR ValuePrevious =
CALCULATE(
SUM(Files[VALUE_EUR]),
Files[Source.Name3] = PreviousDate 
)

RETURN
ROUND(ValueCurrent - ValuePrevious, 2)

could you please help me to achieve a correct result ? Please let me know if you have any idea of doing it other than creating a calculated column, 

Also i would like to mention that i want to use the result in a matrix visual along with other metrics. So any filter in the matrix visual that affects other metrics , i guess is not a good solution for this. 

[V-yubandi-msft]

Hi @ImSh7m ,

We would appreciate it if you could let us know whether your concern has been resolved or if you still require assistance. Your feedback may also be useful to others facing similar situations.

Thank You.

[burakkaragoz]

Hi @ImSh7m ,

Both answers are on the right track, but there's a simpler approach that'll be more reliable. Your main issue is that you're overcomplicating the CALCULATE logic.

Here's a cleaner version:

ValueDiff = 
VAR CurrentInvestmentID = Files[InvestmentID]
VAR CurrentQuarter = Files[Quartal_Jahr]
VAR CurrentSource = Files[Source.Name3]
VAR CurrentValue = Files[VALUE_EUR]

VAR PreviousSource = 
    SWITCH(
        RIGHT(CurrentSource, 8),  // Gets the _x+NN part
        "_x+Final", CurrentQuarter & "_x+10",
        "_x+10", CurrentQuarter & "_x+09",
        "_x+09", CurrentQuarter & "_x+08",
        "_x+08", CurrentQuarter & "_x+07",
        "_x+07", CurrentQuarter & "_x+06",
        "_x+06", CurrentQuarter & "_x+05",
        "_x+05", CurrentQuarter & "_x+04",
        BLANK()
    )

VAR PreviousValue = 
    CALCULATE(
        SUM(Files[VALUE_EUR]),
        Files[InvestmentID] = CurrentInvestmentID,
        Files[Quartal_Jahr] = CurrentQuarter,
        Files[Source.Name3] = PreviousSource
    )

RETURN
IF(
    Files[ValueChangeStatus] = "Change" && NOT(ISBLANK(PreviousValue)),
    ROUND(CurrentValue - PreviousValue, 2),
    BLANK()
)

Key improvements:

• Uses RIGHT() function to extract the suffix, making it more flexible
• Simpler CALCULATE filter logic
• Adds check for blank previous values
• Uses current row's value directly instead of recalculating it

Why your original didn't work: You were missing the InvestmentID filter, so it was summing across all investments for that date. The second answer (Hasan) spotted that correctly.

This should work properly in your matrix visual and handle the context correctly when other filters are applied.

---

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 assisted by AI for translation and formatting purposes.

[V-yubandi-msft]

Hi @ImSh7m ,

Thanks for reaching out on the Fabric Community.

I’ve validated the DAX shared by @VahidDM , and it’s working correctly it returns the expected results based on your requirements. I recommend reviewing his response in detail, and let us know if you’d like to make any adjustments.

For your reference, I’ve also attached the PBIX file so you can cross check the logic in your environment.

Appreciate your prompt engagement in the community @VahidDM .

Warm regards,
Yugandhar.

[Hasan_]

Your calculated column may not be working because it's missing the correct filter context for the InvestmentID. The CALCULATE function is summing the VALUE_EUR for all investments that match the date criteria, not just the specific investment of the current row.

Try using variables to store the InvestmentID of the current row and then applies it as a filter within the CALCULATE functions.

VAR CurrentInvestmentID = Files[InvestmentID]
VAR CurrentSource = Files[Source.Name3]
VAR YearQuarter = Files[Quartal_Jahr]

.......

VAR ValueCurrent =
CALCULATE(
SUM(Files[VALUE_EUR]),
FILTER(
ALL(Files),
Files[InvestmentID] = CurrentInvestmentID &&
Files[Source.Name3] = CurrentSource
)
)
VAR ValuePrevious =
CALCULATE(
SUM(Files[VALUE_EUR]),
FILTER(
ALL(Files),
Files[InvestmentID] = CurrentInvestmentID &&
Files[Source.Name3] = PreviousSource
)
)

.......

[VahidDM · Accepted solution]

Hi @ImSh7m 

Here’s the DAX for your Files table:

ValueDiff =
VAR CurrentInv    = Files[InvestmentID]
VAR CurrentYQ     = Files[Quartal_Jahr]
VAR CurrSource    = Files[Source.Name3]
VAR CurrVal       = Files[VALUE_EUR]

VAR PrevSource =
    SWITCH(
        TRUE(),
        CurrSource = CurrentYQ & "_x+Final", CurrentYQ & "_x+10",
        CurrSource = CurrentYQ & "_x+10",    CurrentYQ & "_x+09",
        CurrSource = CurrentYQ & "_x+09",    CurrentYQ & "_x+08",
        CurrSource = CurrentYQ & "_x+08",    CurrentYQ & "_x+07",
        CurrSource = CurrentYQ & "_x+07",    CurrentYQ & "_x+06",
        CurrSource = CurrentYQ & "_x+06",    CurrentYQ & "_x+05",
        CurrSource = CurrentYQ & "_x+05",    CurrentYQ & "_x+04",
        BLANK()
    )

VAR PrevVal =
    CALCULATE(
        MAX(Files[VALUE_EUR]),
        FILTER(
            ALL(Files),
            Files[InvestmentID] = CurrentInv
            && Files[Quartal_Jahr] = CurrentYQ
            && Files[Source.Name3] = PrevSource
        )
    )

RETURN
IF(
    Files[ValueChangeStatus] = "Change",
    ROUND(CurrVal - PrevVal, 2),
    BLANK()
)

Explanation:

1. PrevSource uses SWITCH to map each “x+NN” to its prior step.
2. PrevVal then pulls the VALUE_EUR for that prior step (same InvestmentID & YearQuarter).
3. The final IF only calculates the difference when ValueChangeStatus = "Change".

Put this column in your matrix alongside your other metrics without extra filters.

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube