Dax needed

[PowerBIBeginer]

Hi All,

In the matrix,

- I've pulled my date column in rows,

- in columns have pulled types,

- and in values no. of Id's pulled..

in columns, they're 4 types say a, b, c and d.

What I need is 2 things -

1 is --> after January 2021 i should get January 2022 in down row then January 2023 in down row then February 2021 and so on..

2 is --> After "d" value column i should get Jan 22 - Jan 21 / Jan 21 output and it is for every row. So I'm looking for difference..

Can I please someone guide / help on this..

[v-yanjiang-msft · Accepted solution]

Hi @PowerBIBeginer ,

According to your description, I create a sample.

Here's my solution.

1.In Power Query, create a rank column, you can copy-paste the below code in a blank query to see the details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdJLbsNADIPhu3idAiPNe9leI8j9r1GbLUSBA8ObD15EzP9+X17cvu7nel3f92vX5/WPHujEGliJLbARe2AnjsBBnIGTuAIXcQduohX++pI4HZWuMp5lcZfnBSo1fduo3MA6lSPYoHIFm1TOYIvKHWxTOYQXKpdwS1dwCs/H8TpP16UpPP1xfx//PNq1BujQHKBTe4AuDQK6tYhHa9EkoKZNQF2jgNajCnA7qgD3owrw0CqgU6uALq0CurWKR1vRKqCmVUBdq4BWrQLatApoP6oAj6MK8DyqAN/nfX4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Column" = _t, Type = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Column", type date}, {"Type", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.Month([Date Column])),
    #"Added rank"=Table.AddRankColumn(#"Added Custom","Custom2",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Added rank", "Rank", each [Custom2]+Date.Year([Date Column])-2021),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Custom2"})
in
    #"Removed Columns"

Result:

2.Sort the Date column by Rank column.

3.Create a new table with one column, containing all the types and "Diff". Then relate the two tables with type column.

4.Create a measure.

Measure =
IF (
    MAX ( 'Table (2)'[Type] ) = "Diff",
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Type] = "A" ), 'Table'[Value] )
        - MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Type] = "B" ), 'Table'[Value] ),
    MAX ( 'Table'[Value] )
)

Put Type from table2 in matrix columns and measure in matrix values, get the result.

I attach my file below for your reference.

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

Best regards,

Community Support Team_yanjiang

[v-yanjiang-msft · Accepted solution]

Hi @PowerBIBeginer ,

According to your description, I create a sample.

Here's my solution.

1.In Power Query, create a rank column, you can copy-paste the below code in a blank query to see the details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdJLbsNADIPhu3idAiPNe9leI8j9r1GbLUSBA8ObD15EzP9+X17cvu7nel3f92vX5/WPHujEGliJLbARe2AnjsBBnIGTuAIXcQduohX++pI4HZWuMp5lcZfnBSo1fduo3MA6lSPYoHIFm1TOYIvKHWxTOYQXKpdwS1dwCs/H8TpP16UpPP1xfx//PNq1BujQHKBTe4AuDQK6tYhHa9EkoKZNQF2jgNajCnA7qgD3owrw0CqgU6uALq0CurWKR1vRKqCmVUBdq4BWrQLatApoP6oAj6MK8DyqAN/nfX4B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Date Column" = _t, Type = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date Column", type date}, {"Type", type text}, {"Value", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.Month([Date Column])),
    #"Added rank"=Table.AddRankColumn(#"Added Custom","Custom2",{"Custom"}),
    #"Added Custom1" = Table.AddColumn(#"Added rank", "Rank", each [Custom2]+Date.Year([Date Column])-2021),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Custom2"})
in
    #"Removed Columns"

Result:

2.Sort the Date column by Rank column.

3.Create a new table with one column, containing all the types and "Diff". Then relate the two tables with type column.

4.Create a measure.

Measure =
IF (
    MAX ( 'Table (2)'[Type] ) = "Diff",
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Type] = "A" ), 'Table'[Value] )
        - MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Type] = "B" ), 'Table'[Value] ),
    MAX ( 'Table'[Value] )
)

Put Type from table2 in matrix columns and measure in matrix values, get the result.

I attach my file below for your reference.

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

Best regards,

Community Support Team_yanjiang

[PowerBIBeginer · Accepted solution]

Thank You for so detailed explanation and fantastic solution!.. Thank you for kind attention 

[PowerBIBeginer]

Hi Board Members, could anyone please help here

[PowerBIBeginer]

any help on the same.. pls guide