Forum Discussion

Gaurav_84's avatar
Gaurav_84
Helper I
1 year ago
Solved

Dex calculating cumulative value

Hi Experts,

 

I am stuck with below issue & i tried almost all the Dex formula to calculate the values in my Powerbi report ( YTD, PREVIOUSYEAR etc...). But no success looking for your valuable inputs.

 

Below is my backend data.

 

Below is how my report should display this data.

 

Regards

Gaurav

 

Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Gaurav_84 ,
    As Selva-Salimi said, first you need to remove the unnecessary columns, and after Iain you need the period columns for the inverse perspective.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUTI0AAEgw8jAyATEN4RwDWGiYMoYQpmAqFgdYjVjoaCajbBpNiLSZnyacVNQzcbYNBsTaTM+zbip2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Org = _t, Account = _t, Year = _t, #"Carry Fowrd" = _t, #"Period 1" = _t, #"Period 2" = _t, #"Period 3" = _t, #"Period 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Org", type text}, {"Account", Int64.Type}, {"Year", Int64.Type}, {"Carry Fowrd", Int64.Type}, {"Period 1", Int64.Type}, {"Period 2", Int64.Type}, {"Period 3", Int64.Type}, {"Period 4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Org", "Account", "Year", "Carry Fowrd"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Period"}})
in
    #"Renamed Columns"

    Then append the tables and create a calculate table

    Filter Table = 
SUMMARIZE(
    'Table',
    'Table'[Org],
    'Table'[Year],
    'Table'[Period]
)

    Use filter table as slicer filed and create measures

    Current Period = 
VAR _Year = SELECTEDVALUE('Filter Table'[Year])
VAR _Period = SELECTEDVALUE('Filter Table'[Period])
VAR _Org = SELECTEDVALUE('Filter Table'[Org])
VAR _CarryFowrd = 
CALCULATE(
    MAX('Table'[Carry Fowrd]),
    FILTER(
        'Table',
        'Table'[Org] = _Org && 'Table'[Year] = _Year && 'Table'[Period] = _Period
    )
)
VAR _SumPeriod = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Org] = _Org && 'Table'[Year] = _Year && 'Table'[Period] = _Period
    )
)
RETURN
_CarryFowrd + _SumPeriod
    Previous Period = 
VAR _Year = SELECTEDVALUE('Filter Table'[Year])
VAR _Period = SELECTEDVALUE('Filter Table'[Period])
VAR _PreviousPeriod = CONCATENATE("Period ",RIGHT(_Period,1)-1)
VAR _Org = SELECTEDVALUE('Filter Table'[Org])
VAR _CarryFowrd = 
CALCULATE(
    MAX('Table'[Carry Fowrd]),
    FILTER(
        'Table',
        'Table'[Org] = _Org && 'Table'[Year] = _Year && 'Table'[Period] = _PreviousPeriod
    )
)
VAR _SumPeriod = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Org] = _Org && 'Table'[Year] = _Year && 'Table'[Period] = _PreviousPeriod
    )
)
RETURN
_CarryFowrd + _SumPeriod
    Same period last year = 
VAR _Year = SELECTEDVALUE('Filter Table'[Year])
VAR _Period = SELECTEDVALUE('Filter Table'[Period])
VAR _Org = SELECTEDVALUE('Filter Table'[Org])
VAR _CarryFowrd = 
CALCULATE(
    MAX('Table'[Carry Fowrd]),
    FILTER(
        'Table',
        'Table'[Org] = _Org && 'Table'[Year] = _Year - 1 && 'Table'[Period] = _Period
    )
)
VAR _SumPeriod = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Org] = _Org && 'Table'[Year] = _Year - 1 && 'Table'[Period] = _Period
    )
)
RETURN

    Final output

     

    Best regards,
    Albert He

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

     

4 Replies

  • Selva-Salimi's avatar
    Selva-Salimi
    Solution Sage
    1 year ago

    Hi Gaurav_84 

     

    I recommend to unpivot your table using unpivot columns option, in transform data . then your table would be have these columns...

    Org   Account     year     period    value

     

    then it will be easy to write these measures. follow these steps and rewrite your measure. if you have any question feel free to ask.

     

    If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

    • Gaurav_84's avatar
      Gaurav_84
      Helper I
      1 year ago

      Thanks Selva,

       

      This data is not in Pivot format this is how it looks like in my backend table. Is there any other way to achive it?

    • Gaurav_84's avatar
      Gaurav_84
      Helper I
      1 year ago

      Hi Selva,

       

      Do you think data in below format will work? If Yes, can you please suggest what measure i will write to get expected value in current period & previous period.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Gaurav_84 ,
    As Selva-Salimi said, first you need to remove the unnecessary columns, and after Iain you need the period columns for the inverse perspective.

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQwMFTSUTI0AAEgw8jAyATEN4RwDWGiYMoYQpmAqFgdYjVjoaCajbBpNiLSZnyacVNQzcbYNBsTaTM+zbip2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Org = _t, Account = _t, Year = _t, #"Carry Fowrd" = _t, #"Period 1" = _t, #"Period 2" = _t, #"Period 3" = _t, #"Period 4" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Org", type text}, {"Account", Int64.Type}, {"Year", Int64.Type}, {"Carry Fowrd", Int64.Type}, {"Period 1", Int64.Type}, {"Period 2", Int64.Type}, {"Period 3", Int64.Type}, {"Period 4", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Org", "Account", "Year", "Carry Fowrd"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Columns",{{"Attribute", "Period"}})
in
    #"Renamed Columns"

    Then append the tables and create a calculate table

    Filter Table = 
SUMMARIZE(
    'Table',
    'Table'[Org],
    'Table'[Year],
    'Table'[Period]
)

    Use filter table as slicer filed and create measures

    Current Period = 
VAR _Year = SELECTEDVALUE('Filter Table'[Year])
VAR _Period = SELECTEDVALUE('Filter Table'[Period])
VAR _Org = SELECTEDVALUE('Filter Table'[Org])
VAR _CarryFowrd = 
CALCULATE(
    MAX('Table'[Carry Fowrd]),
    FILTER(
        'Table',
        'Table'[Org] = _Org && 'Table'[Year] = _Year && 'Table'[Period] = _Period
    )
)
VAR _SumPeriod = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Org] = _Org && 'Table'[Year] = _Year && 'Table'[Period] = _Period
    )
)
RETURN
_CarryFowrd + _SumPeriod
    Previous Period = 
VAR _Year = SELECTEDVALUE('Filter Table'[Year])
VAR _Period = SELECTEDVALUE('Filter Table'[Period])
VAR _PreviousPeriod = CONCATENATE("Period ",RIGHT(_Period,1)-1)
VAR _Org = SELECTEDVALUE('Filter Table'[Org])
VAR _CarryFowrd = 
CALCULATE(
    MAX('Table'[Carry Fowrd]),
    FILTER(
        'Table',
        'Table'[Org] = _Org && 'Table'[Year] = _Year && 'Table'[Period] = _PreviousPeriod
    )
)
VAR _SumPeriod = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Org] = _Org && 'Table'[Year] = _Year && 'Table'[Period] = _PreviousPeriod
    )
)
RETURN
_CarryFowrd + _SumPeriod
    Same period last year = 
VAR _Year = SELECTEDVALUE('Filter Table'[Year])
VAR _Period = SELECTEDVALUE('Filter Table'[Period])
VAR _Org = SELECTEDVALUE('Filter Table'[Org])
VAR _CarryFowrd = 
CALCULATE(
    MAX('Table'[Carry Fowrd]),
    FILTER(
        'Table',
        'Table'[Org] = _Org && 'Table'[Year] = _Year - 1 && 'Table'[Period] = _Period
    )
)
VAR _SumPeriod = 
CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        'Table',
        'Table'[Org] = _Org && 'Table'[Year] = _Year - 1 && 'Table'[Period] = _Period
    )
)
RETURN

    Final output

     

    Best regards,
    Albert He

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