Calculate amount difference of current and previous period

[third_hicana]

Hi. Asking for help how can I calculate the difference of capex and opex based on current and previous forecast period. The current forecast period is FY24-09 and the previous is FY24-08.

For example, in 24-May the difference of capex is -10,000 and opex is -5,000. I wish to appear these variances in one column in table visual.

Thank you very much for your help.

-Third

[Anonymous · Accepted solution]

Hi @third_hicana 

Here for your reference:

Then add a measure:

MEASURE =
VAR _currentFYYear =
    MAX ( 'Table'[Forecast Period] )
VAR _previousFYYear =
    CALCULATE (
        MIN ( 'Table'[Forecast Period] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Forecast Period] < _currentFYYear )
    )
VAR _currentType =
    SELECTEDVALUE ( 'Table'[Money Type] )
RETURN
    IF (
        _previousFYYear = BLANK (),
        0,
        CALCULATE (
            SUM ( 'Table'[24-May] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[Money Type] = _currentType
                    && 'Table'[Forecast Period] = _currentFYYear
            )
        )
            - CALCULATE (
                SUM ( 'Table'[24-May] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Money Type] = _currentType
                        && 'Table'[Forecast Period] = _previousFYYear
                )
            )
    )

The result is as follow:

Best Regards

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

[third_hicana]

Thank you very much for your help

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...