Forum Discussion

PFinnegan's avatar
PFinnegan
Regular Visitor
4 years ago

Moving data value up and across

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

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    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

  • v-chenwuz-msft's avatar
    v-chenwuz-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.

     

  • PFinnegan's avatar
    PFinnegan
    Regular Visitor
    4 years ago

    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.