DAX measure that checks coverage of future projection

[learner4eva]

Hi, I need help working out the DAX measure that calculates number of weeks of coverage Q has of P 

For Week 1, Q = 400 and that can cover full of 200 P in Week 2 and 200 out of 300 P in Week 3. Therefore Q in Week 1 can cover 1.67 weeks of P

Similarly in Week 2, Q = 600 and that can cover full of 300 P in Week 3 and 300 out of 400 P in Week 4. Therefore Q in Week 2 can cover 1.75 weeks of P

	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7
P	100	200	300	400	500	600	700
Q	400	600	900	800	1000
R	400 = 200 (Week 2) + 200/300 (Week 3) 1 + 2/3 = 1.67	600 = 300 (Week 3) + 300/400 (Week 4) 1 + 3/4 = 1.75	2	1.5	1.83

Can you advise how that can be done?

[lbendlin · Accepted solution]

Thank you.  First step is to unpivot and then repivot the data to make it usable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClDSUTI0MACSRmDSGEyagElTMGkGJs2BZKxOtFIgXBYibgkmLcAk0BwQpQDGsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, #"Week 1" = _t, #"Week 2" = _t, #"Week 3" = _t, #"Week 4" = _t, #"Week 5" = _t, #"Week 6" = _t, #"Week 7" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Key"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Key]), "Key", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.2", "Week"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute.1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Week", Int64.Type}, {"P", type number}, {"Q", type number}})
in
    #"Changed Type"

Then we can add the required measure:

R = 
var w = SELECTEDVALUE('Table'[Week])
var q = sum('Table'[Q])
var a = ADDCOLUMNS(CALCULATETABLE(VALUES('Table'[Week]),'Table'[Week]>w)
        ,"cm",var wk=[Week] return CALCULATE(sum('Table'[P]),'Table'[Week]>w,'Table'[Week]<=wk)
        ,"pcm",var wk=[Week] return 0+CALCULATE(sum('Table'[P]),'Table'[Week]>w,'Table'[Week]<wk))
var b = ADDCOLUMNS(a,"cv",SWITCH(true(),[cm]<=q,1,[pcm]>q,0,divide(q-[pcm],[cm]-[pcm],0)))
return sumx(b,[cv])

and finally display the result:

See attached.

[lbendlin]

"Similarly in Week 2, Q = 600"

Your sample data says 700.  Please adjust either the data or the statement.

[learner4eva]

Yes it should have been 700, I have corrected that in the post. Thanks

[lbendlin]

"Similarly in Week 2, Q = 700 and that can cover full of 300 P in Week 3 and 300 out of 400 P in Week 4. Therefore Q in Week 2 can cover 1.75 weeks of P"

Why not 2 weeks of P ?

[learner4eva]

Argh should have edited the number in the sample data instead. I have updated the post again. Thanks!

[lbendlin · Accepted solution]

Thank you.  First step is to unpivot and then repivot the data to make it usable.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WClDSUTI0MACSRmDSGEyagElTMGkGJs2BZKxOtFIgXBYibgkmLcAk0BwQpQDGsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Key = _t, #"Week 1" = _t, #"Week 2" = _t, #"Week 3" = _t, #"Week 4" = _t, #"Week 5" = _t, #"Week 6" = _t, #"Week 7" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Key"}, "Attribute", "Value"),
    #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"[Key]), "Key", "Value"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Pivoted Column", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Split Column by Delimiter",{{"Attribute.2", "Week"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"Attribute.1"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Week", Int64.Type}, {"P", type number}, {"Q", type number}})
in
    #"Changed Type"

Then we can add the required measure:

R = 
var w = SELECTEDVALUE('Table'[Week])
var q = sum('Table'[Q])
var a = ADDCOLUMNS(CALCULATETABLE(VALUES('Table'[Week]),'Table'[Week]>w)
        ,"cm",var wk=[Week] return CALCULATE(sum('Table'[P]),'Table'[Week]>w,'Table'[Week]<=wk)
        ,"pcm",var wk=[Week] return 0+CALCULATE(sum('Table'[P]),'Table'[Week]>w,'Table'[Week]<wk))
var b = ADDCOLUMNS(a,"cv",SWITCH(true(),[cm]<=q,1,[pcm]>q,0,divide(q-[pcm],[cm]-[pcm],0)))
return sumx(b,[cv])

and finally display the result:

See attached.

[learner4eva]

Hi  @lbendlin , can you advise how should i use convert this R measure into a column in a table using DAX?

[lbendlin]

You mean that you want a calculated column instead of a measure? Any particular reason?

[learner4eva]

Hi @lbendlin , I would like to be able to filter for items where the R measure is below  certain number. I have been trying to use measures for this but it does not seem to work

[lbendlin]

Right, measures can only be used as visual filters. 

However, if you convert this to a calculated column then the value would no longer be influenced by user interaction.  If that is ok for you please let me know and I can show you the calculated column version of the formula.

[learner4eva]

Hi @lbendlin  thank you for looking into this! Yes I would like to find out how to do the calculated column version of the formula. I have tried multiple times but kept on failing.

I have multiple sets of P and Q which makes deriving R for each set of P and Q even more challenging... 

[lbendlin · Accepted solution]

Here is the calculated column version:

Rc = 
var w = 'Table'[Week]
var p = 'Table'[P]
var q = 'Table'[Q]
var a = ADDCOLUMNS(CALCULATETABLE(VALUES('Table'[Week]),All('Table'),'Table'[Week]>w)
        ,"cm",var wk=[Week] return CALCULATE(sum('Table'[P]),All('Table'),'Table'[Week]>w,'Table'[Week]<=wk)
        ,"pcm",var wk=[Week] return 0+CALCULATE(sum('Table'[P]),All('Table'),'Table'[Week]>w,'Table'[Week]<wk))
var b = ADDCOLUMNS(a,"cv",SWITCH(true(),[cm]<=q,1,[pcm]>q,0,divide(q-[pcm],[cm]-[pcm],0)))
return  sumx(b,[cv])

[learner4eva]

Thank you @lbendlin 

[learner4eva]

Thank you so much @lbendlin ! I have to read through the formula many times to understand it! Thank you! I learnt so much