Cumulative A B C -> 3A+2B+C

[Isonomia]

Hi
My data set is like this

2024 | 2025 [ 2026

A    |     B    |     C

I would like to créate a line chart  whith a dax function like this :

2024 = A

2025 = A+A+B

2026 = A+A+A +B+B+C

Thanx for your help

[barritown · Accepted solution]

Hi @Isonomia,

You can try such a calculated column:

In plain text for convenience:

Result = 
VAR currentYear = [Year]
VAR _tbl1 = FILTER ( data, [Year] <= currentYear )
VAR _tbl2 = ADDCOLUMNS ( _tbl1, "@mult", currentYear - [Year] + 1 )
VAR _tbl3 = ADDCOLUMNS ( _tbl2, "@res", [@mult] * [Value] )
RETURN SUMX ( _tbl3, [@res] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[sanalytics · Accepted solution]

Hello @Isonomia 

Below is my sort of solution for your problem.. This is little bit lengthy but can give you optimum desired result.

Step 1 :
Transform your data in PQ. Below is the complete code.

let
    Source = Data,
    #"Demoted Headers" = Table.DemoteHeaders(Source),
    #"Transposed Table" = Table.Transpose(#"Demoted Headers"),
    #"Renamed Columns" = Table.RenameColumns(#"Transposed Table",{{"Column1", "Year"}, {"Column2", "Item"}, {"Column3", "Value"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Value", Int64.Type}}),
    Year = #"Changed Type"[Year],
    Custom1 = #"Changed Type",
    #"Added Index1" = Table.AddIndexColumn(Custom1, "Index", 1, 1, Int64.Type),
    #"Added Index" = Table.AddIndexColumn(#"Added Index1", "Rows", List.Count(Year), -1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "GenerateRows", each List.Repeat({ [Index] },[Rows])),
    #"Expanded GenerateRows" = Table.ExpandListColumn(#"Added Custom", "GenerateRows"),
    #"Removed Other Columns" = Table.SelectColumns(#"Expanded GenerateRows",{"Year", "Item", "Value", "Index"}),
    #"Added Index2" = Table.AddIndexColumn(#"Removed Other Columns", "Index.1", 1, 1, Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Index2",{{"Index.1", "Flag"}})
in
    #"Renamed Columns1"

 Step 2 :
Create a Calculated Column for generating new year. Below is the DAX code.

NewYear = 
VAR _YearFlag = 
RANK(
    SKIP,SUMMARIZE( ALLSELECTED( 'Table' ),'Table'[Year],'Table'[Item],'Table'[Value],'Table'[Index],'Table'[Flag] ),
    ORDERBY( 'Table'[Flag],ASC ),,PARTITIONBY( 'Table'[Item] )
) - 1
VAR _Result = 
 'Table'[Year] + _YearFlag

RETURN
_Result

Step 3 :

Create a cumulative measure. Below is the DAX code

Cumulative Measure = 
 CALCULATE(
    SUM( 'Table'[Value] ),
    WINDOW( 
        1,ABS,
        0,REL,
    SUMMARIZE( ALLSELECTED('Table' ),'Table'[Year],'Table'[Item],'Table'[Flag],'Table'[Value],'Table'[NewYear] ),
    ORDERBY( 'Table'[Flag], ASC ),
    PARTITIONBY( 'Table'[Item] )
    ) )

Below is the  output ss

Below is the attached pbix file.

https://www.transfernow.net/dl/20240425Hapf5UDK

Hope, it will help you.

Regards

sanalytics

[barritown · Accepted solution]

Hi @Isonomia,

You can try such a calculated column:

In plain text for convenience:

Result = 
VAR currentYear = [Year]
VAR _tbl1 = FILTER ( data, [Year] <= currentYear )
VAR _tbl2 = ADDCOLUMNS ( _tbl1, "@mult", currentYear - [Year] + 1 )
VAR _tbl3 = ADDCOLUMNS ( _tbl2, "@res", [@mult] * [Value] )
RETURN SUMX ( _tbl3, [@res] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian