Create measure with cumulative objectives

Hi , Anonymous 

Here are the steps you can refer to :

(1)This is my test data :

(2)We need to unpivot the table and add a flag column in Power Query Editor, you can put this in your "Advanced Editor" in your Power Query Editor:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8nfyUvDNzFPSUTIyJZeI1YlWCssszqxMLVZIKVXIzc8pBkqYQbEhSJWxAYgAsSxAQmAeEJuaweRAUkqxsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [visites = _t, JAN = _t, FEB = _t, MAR = _t, APR = _t, MAY = _t, JUN = _t, JUL = _t, AUG = _t, SEP = _t, OCT = _t, NOV = _t, DEC = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"visites", type text}, {"JAN", Int64.Type}, {"FEB", Int64.Type}, {"MAR", Int64.Type}, {"APR", Int64.Type}, {"MAY", Int64.Type}, {"JUN", Int64.Type}, {"JUL", Int64.Type}, {"AUG", Int64.Type}, {"SEP", Int64.Type}, {"OCT", Int64.Type}, {"NOV", Int64.Type}, {"DEC", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"visites"}, "Attribute", "Value"),
    #"Added Conditional Column" = Table.AddColumn(#"Unpivoted Columns", "Custom", each if [Attribute] = "JAN" then 1 else if [Attribute] = "FEB" then 2 else if [Attribute] = "MAR" then 3 else if [Attribute] = "APR" then 4 else if [Attribute] = "MAY" then 5 else if [Attribute] = "JUN" then 6 else if [Attribute] = "JUL" then 7 else if [Attribute] = "AUG" then 8 else if [Attribute] = "SEP" then 9 else if [Attribute] = "OCT" then 10 else if [Attribute] = "NOV" then 11 else if [Attribute] = "DEC" then 12 else null, type number)
in
    #"Added Conditional Column"

The table will convert to this:

(3)Then we apply the data to Desktop , and we can create a measure :

Measure = var _current_month = SELECTEDVALUE('Table'[Custom])
var _current_vis =SELECTEDVALUE('Table'[visites])
var _t =FILTER( ALLSELECTED('Table') ,'Table'[Custom] <= _current_month && 'Table'[visites] = _current_vis)
return
SUMX(_t,[Value])

(4)Then we can put the field on the visual we need , and we can sort the "Month" column by the [Custom] column , we can meet your need , the result is as follows:

Best Regards,

Aniya Zhang

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

Hi,

Share the download link of your PBI file.

Hello Aniya,

Thank you for your answer.

I've tried this solution but it doesn't works.

I'm not advanced on power Bi, that why so I have some questions that will seams easy for you.

1) Where did you save the json files ? The dataset with the objectives needs to be dynamic because it's related to the number of employees. nb of employees*2/12--> Do I need to create my own dataset ?  where shouldI save it ?

2) The measure doesn't show data. I had create the connexion between this table and the calendar table.

Thanks a lot for your help.

Best regards.