Forum Discussion
Using slicer for two sheets or calculating change for waterfall chart on one page
You really need a calendar table with an ID column (integer that continuously advances by 1 for every month). WIth this set up, you can create a lifetime to date calculation and subtract the lifetime to date from the previous lifetime to date for the prior month. Read my blog about calendar tables,http://exceleratorbi.com.au/power-pivot-calendar-tables/
then write a formula something like this.
LTD = calculate (sum(data[value]),filter(all(calendar),calendar[ID] <= max(calendar[ID]))
chg vs last month = [LTD] - calculate (sum(data[value]),filter(all(calendar),calendar[ID] <= max(calendar[ID])-1)
You really need a calendar table with an ID column (integer that continuously advances by 1 for every month). WIth this set up, you can create a lifetime to date calculation and subtract the lifetime to date from the previous lifetime to date for the prior month. Read my blog about calendar tables,http://exceleratorbi.com.au/power-pivot-calendar-tables/
then write a formula something like this.
LTD = calculate (sum(data[value]),filter(all(calendar),calendar[ID] <= max(calendar[ID]))
chg vs last month = [LTD] - calculate (sum(data[value]),filter(all(calendar),calendar[ID] <= max(calendar[ID])-1)
The day I posted this was the day I started using Power BI for the first time, so I needed some time to understand what you said, but I did it and it works, thank you!
(The pictures were just very dumbed-down examples, I'm using YYYYMM to identify the months, not this jan, feb, etc, of course. :) )