Dex calculating cumulative value

[Gaurav_84]

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

[Anonymous · Accepted solution]

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

[Selva-Salimi]

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]

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.

[Gaurav_84]

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?