Moving data value up and across

[PFinnegan]

Looking for some help on a calculation I'm trying to create on this report I'm looking to build. I've reached out for support and tried a few things but just can't work out how to do this.

So I have this data:

So the numbers are pulling through correctly as I want them however, the ones highlighted yellow I'm trying to get to align in Column 3.

So basically the logic should go something like this -

If month 00 - doesn't move.

If 01 moves forward 1 fiscal month.

If 02 moves forward 2 fiscal months

and so on....

The sum figures for the value are calculated as follows : 

Total Cash Month on Month Adjustment (test) =

VAR EarlierTime =

    CALCULATE (

        MAX ( View_D_DynamicAgeing[Month] ),

        FILTER (

            ALLSELECTED ( View_D_DynamicAgeing[Month] ),

            View_D_DynamicAgeing[Month] < SELECTEDVALUE ( View_D_DynamicAgeing[Month] )

        )

    )

VAR EarlierMeasureValue =

    CALCULATE ( [Total Cash] , View_D_DynamicAgeing[Month] = EarlierTime )

RETURN

  CALCULATE(

  Measure_Table[Total Cash] -

  EarlierMeasureValue)

Any ideas would be really appreciated.

Thanks

[PFinnegan]

Please see snip of solution I'm looking for: 

I've included the logic, column/row names and the data types of each data source that make up this matrix.

[v-chenwuz-msft]

Hi @PFinnegan ,

Assume that the column field in the matrix table is [number], then use the following formula to move the rows.

Total Cash Month on Month Adjustment (test) =

VAR EarlierTime =

    CALCULATE (

        MAX ( View_D_DynamicAgeing[Month] ),

        FILTER (

            ALLSELECTED ( View_D_DynamicAgeing[Month] ),

            View_D_DynamicAgeing[Month] <

            ( SELECTEDVALUE ( View_D_DynamicAgeing[Month] ) + VALUE ( SELECTEDVALUE ( 'View_D_DynamicAgeing'[number] ) ) - 1)

        )

    )

VAR EarlierMeasureValue =

    CALCULATE ( [Total Cash] , View_D_DynamicAgeing[Month] = EarlierTime )

RETURN

  CALCULATE(

  Measure_Table[Total Cash] -

  EarlierMeasureValue)

Best Regards

Community Support Team _ chenwu zhu

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

[lbendlin]

Please provide sanitized sample data that fully covers your issue. I cannot help you without usable sample data.
Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. I cannot use screenshots of your source data.
Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523