Forum Discussion

dianis's avatar
dianis
Microsoft Employee
4 years ago

Complex Compound growth formula - dynamic

Hi all, 

I am looking to build a measure in PowerBI that allows me to calculate a new column called baseline in the table1 as per formulas below:

The challenge is tha CM= Current Month is dynamic depending on the month we are. As of today CM=February and CM+1= March; however in a month time CM=March and CM+1=April.

The first formula needs to be applied till Jun-22 and after that second formula should be applied. 

Below a sample of the table I am working with.

I should be able to aggregate the result and any level as per columns in the table (Channel, Client or Product)

Any help is well appreciated!

Thanks

Diana

 

Table1:

Month KeyMonthDaysofMonthRelative MonthChannelClient IDProduct Avg Daily Usage Avg Daily Usage Growth MoM %
391Jan-2231CM-1Direct1006751ABC                            3734%
391Jan-2231CM-1Direct1006751XYZ                            49-4%
391Jan-2231CM-1Direct1006751HJK                              10%
391Jan-2231CM-1Direct1006909ABC                       1,5175%
391Jan-2231CM-1Direct1006909XYZ                          4535%
391Jan-2231CM-1Direct1006909HJK                          27418%
391Jan-2231CM-1Tele Sales937586ABC                     37,0489%
391Jan-2231CM-1Tele Sales937586XYZ                     25,7236%
391Jan-2231CM-1Tele Sales937586HJK                       5,1211%
392Feb-2228CMDirect1006751ABC  
392Feb-2228CMDirect1006751XYZ  
392Feb-2228CMDirect1006751HJK  
392Feb-2228CMDirect1006909ABC  
392Feb-2228CMDirect1006909XYZ  
392Feb-2228CMDirect1006909HJK  
392Feb-2228CMTele Sales937586ABC  
392Feb-2228CMTele Sales937586XYZ  
392Feb-2228CMTele Sales937586HJK  
393Mar-2231CM+1Direct1006751ABC  
393Mar-2231CM+1Direct1006751XYZ  
393Mar-2231CM+1Direct1006751HJK  
393Mar-2231CM+1Direct1006909ABC  
393Mar-2231CM+1Direct1006909XYZ  
393Mar-2231CM+1Direct1006909HJK  
393Mar-2231CM+1Tele Sales937586ABC  
393Mar-2231CM+1Tele Sales937586XYZ  
393Mar-2231CM+1Tele Sales937586HJK  
394Apr-2230CM+2Direct1006751ABC  
394Apr-2230CM+2Direct1006751XYZ  
394Apr-2230CM+2Direct1006751HJK  
394Apr-2230CM+2Direct1006909ABC  
394Apr-2230CM+2Direct1006909XYZ  
394Apr-2230CM+2Direct1006909HJK  
394Apr-2230CM+2Tele Sales937586ABC  
394Apr-2230CM+2Tele Sales937586XYZ  
394Apr-2230CM+2Tele Sales937586HJK  
395May-2231CM+3Direct1006751ABC  
395May-2231CM+3Direct1006751XYZ  
395May-2231CM+3Direct1006751HJK  
395May-2231CM+3Direct1006909ABC  
395May-2231CM+3Direct1006909XYZ  
395May-2231CM+3Direct1006909HJK  
395May-2231CM+3Tele Sales937586ABC  
395May-2231CM+3Tele Sales937586XYZ  
395May-2231CM+3Tele Sales937586HJK  
396Jun-2230CM+4Direct1006751ABC  
396Jun-2230CM+4Direct1006751XYZ  
396Jun-2230CM+4Direct1006751HJK  
396Jun-2230CM+4Direct1006909ABC  
396Jun-2230CM+4Direct1006909XYZ  
396Jun-2230CM+4Direct1006909HJK  
396Jun-2230CM+4Tele Sales937586ABC  
396Jun-2230CM+4Tele Sales937586XYZ  
396Jun-2230CM+4Tele Sales937586HJK  
397Jul-2231CM+5Direct1006751ABC  
397Jul-2231CM+5Direct1006751XYZ  
397Jul-2231CM+5Direct1006751HJK  
397Jul-2231CM+5Direct1006909ABC  
397Jul-2231CM+5Direct1006909XYZ  
397Jul-2231CM+5Direct1006909HJK  
397Jul-2231CM+5Tele Sales937586ABC  
397Jul-2231CM+5Tele Sales937586XYZ  
397Jul-2231CM+5Tele Sales937586HJK  
Need Help

1 Reply

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    As long as this data source is accessed in import mode and refreshed at least once a month (ideally at the beginning of the month) you can easily create that column in Power Query. 

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nZRNS8NAFEX/Sgi4m8J8ZDLJsioiEVe6UEsXqWQhhCJVF/5782ILlmEm91pIScKcx3lz32SzKV1rSlV2/X5l7XTj5On67TC8fk43Rus6eHm1vrya/ovMzwXBq4tyq+CqT88vC1Wrdlqw4qredncLVYtC1mmwaqvbpR0wyhvp31Mll9qvvKNrLjVvQyXrm3TRx2Eciod+HD6mh9YF39T59l1QumpkMVs0swHWq2Cl/Zotmt0Br4wV1JyqSrGbYfdb1TbZ6Z8vAju2x2LHBnDsbEBZjJY8m7Mslp2kf5CIanYm/pAyWvf9AfrqsVjsCWG0ZCp3CKMlU7nHGJo7SiKqaO7y9Vu/n0iN5g5hsSeE0ZKp3CGMlkzlHmNo7iiJqKK5+3livtnzDmGxJ4TRkqncIYyWTOUeY2juKImoornL2+5rz553CIs9IYyWTOUOYbRkKvcYQ3NHSUQVzT3M5MiedwiLPSGMlkzlDmG0ZCr3GENzR0lEFch9+wM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Month Key" = _t, Month = _t, DaysofMonth = _t, Channel = _t, #"Client ID" = _t, Product = _t, #" Avg Daily Usage " = _t, #"Avg Daily Usage Growth MoM %" = _t]),
    #"Replaced Value" = Table.ReplaceValue(Source,"-"," 1 20",Replacer.ReplaceText,{"Month"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Replaced Value",{{"Month", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Relative Month", each let 
   rm = Date.Year([Month])*12+Date.Month([Month])-Date.Year( DateTime.LocalNow())*12-Date.Month(DateTime.LocalNow())
in
   if rm = 0 then "CM" else if rm < 0 then "CM" & Text.From(rm) else "CM+" & Text.From(rm))
in
    #"Added Custom"
    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".