topic Calculate amount difference of current and previous period in DAX Commands and Tips

Calculate amount difference of current and previous period

third_hicana — Tue, 06 Aug 2024 13:48:23 GMT

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

Re: Calculate amount difference of current and previous period

lbendlin — Wed, 07 Aug 2024 01:08:20 GMT

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-Forum/ba-p/963216

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/1447523

Re: Calculate amount difference of current and previous period

Anonymous — Wed, 07 Aug 2024 02:26:05 GMT

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.

Re: Calculate amount difference of current and previous period

third_hicana — Wed, 07 Aug 2024 07:05:42 GMT

Thank you very much for your help