Last month from last visible month in table

[hylosko]

Hello experts!!!

In PBI i have sth like this. I need measure which will show me value for last month from last visible month in table.

For example: for 1. it will be 14, for 2. it will be 28, for 3. it will be 94, for 4. it will be 12 

any ideas ?

[hylosko]

I think it will be not work, if we have more months

my situation i like in this picture 

have you maybe other idea for this case ? 

[Jihwan_Kim · Accepted solution]

Hi,

Check the below.

If you need to add more columns but the below measure does not work, please mention.

Expected measure: = 
VAR _maxmonthnumber =
    MAXX ( FILTER ( Data, Data[Value] <> BLANK () ), Data[Monthnumber] )
RETURN
    IF (
        HASONEVALUE ( Data[Index] ),
        CALCULATE ( SUM ( Data[Value] ), Data[Monthnumber] = _maxmonthnumber - 1 )
    )

[hylosko]

but what if i have situation like this 

[monthnumber] is from calendar table

[index] is from indexestable

[value] is measure

how i should transform this dax ?

[Jihwan_Kim]

Hi,

Thank you for your feedback. If some of components are coming from different tables, I think the relationships between tables has to be known. Please share your sample pbix file, and then I can try to look into it.

Thanks. 

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

I assume the table structure needs to be transformed in Power Query Editor like the sample pbix file.

Power Query Editor

let
    Source = Source,
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Added Index", {"Index"}, "Attribute", "Value"),
    #"Grouped Rows" = Table.Group(#"Unpivoted Other Columns", {"Index"}, {{"ALL", each Table.AddIndexColumn( _, "Monthnumber",1,1) }}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Attribute", "Value", "Monthnumber"}, {"Attribute", "Value", "Monthnumber"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded ALL",{{"Attribute", type text}, {"Value", Int64.Type}, {"Monthnumber", Int64.Type}})
in
    #"Changed Type"

And then, load the data and create a measure like below.

 

Expected measure: =
VAR _maxmonthnumber =
    MAX ( Data[Monthnumber] )
RETURN
    IF (
        HASONEVALUE ( Data[Index] ),
        CALCULATE ( SUM ( Data[Value] ), Data[Monthnumber] = _maxmonthnumber - 1 )
    )