summerize all of previous months given a variable

[yansong]

I use power query to import the data from database with current year beginning balance( C.BEG.BAL) and current month1 (C.M1), which is January, current month 2 (C.M2), which is Febuary and etc.  and also previou year beginning balance (P.BEG.BAL) and previous month current balance (P.M1). I wonder if i can summerize current YTD balance and previous YTD by given a random month. Thank you!

[v-danhe-msft]

Hi @yansong,

From your description, I could not understand the sentence "summerize current YTD balance and previous YTD by given a random month" post in your problem, could you please post your desires result if possible?

Regards,

Daniel He

[yansong]

Sure. If I select February for exmaple, it will summarize C.BEG.BAL+C.M1+C.M2 (Current YTD) and also P.BEG.BAL+P.M1+P.M2( Previous YTD). Let me know if you are still confused. Thank you for your help.

[v-danhe-msft · Accepted solution]

Hi @yansong,

Based on my test, you coud refer to below steps:

1.Copy your row table. Remove the previous month value in row table and remove the currently month value in copied table.

2.Transpose the tables and add index.

3.Merge the two tables and delete one index columns. Rename the other index column as "Month". Apply it.

4. Create a measue and you could get the result.

Measure = CALCULATE(SUM('Merge1'[Value]),FILTER(ALL('Merge1'),'Merge1'[Month]<=MAX('Merge1'[Month])))+CALCULATE(SUM('Merge1'[Table1.Value]),FILTER(ALL('Merge1'),'Merge1'[Month]<=MAX('Merge1'[Month])))

You can also download the PBIX file to have a view.

https://www.dropbox.com/s/wdypfheyfro5qyc/summerize%20all%20of%20previous%20months%20given%20a%20var...

Regards,

Daniel He

[yansong]

Thank you, Daniel! I think that works!