YTD where months are column headings

[KSt]

Hi,

I'm fairly new to Power BI, so would appreciate some help. I have a budget table (simplified example below) and I'd like a calculation to show my total budget for the year to date.

So in July I'd want the Fundraising YTD budget to show £4000 and the Finance YTD budget to show £1450. I'd like this to automatically update itself each month.

NL	CC	Name	Project	Dept	Apr-16	May-16	Jun-16	Jul-16	Aug-16	Sep-16	etc
4000	1	Fundraising	CLS	Vol	1000	1000	1000	1000	1000	1000
4001	1	Finance	CLS	Acc	850		600		916
4002	2	Sales	MT	Acc	500	500	500	500	500	500
4003	4	Telephone	BR	Int	100			100

 Thank you for your help

[v-qiuyu-msft · Accepted solution]

Hi @KSt,

From your description, it seems you are using the matrix to display the values, and the “Apr-16”,”May-16”,…. come from one column field, right?

Assume the sample data likes below:

You can create a measure like this:

Cumulative = CALCULATE(SUM(Table1[Value]),FILTER(ALL('Table1'),'Table1'[Date]<=MAX('Table1'[Date])),VALUES(Table1[NL]),VALUES(Table1[CC]),VALUES(Table1[Name]),VALUES(Table1[Project]),VALUES(Table1[Dept]))

Then create a matrix like below:

Best Regards,
Qiuyun Yu

[v-qiuyu-msft · Accepted solution]

Hi @KSt,

From your description, it seems you are using the matrix to display the values, and the “Apr-16”,”May-16”,…. come from one column field, right?

Assume the sample data likes below:

You can create a measure like this:

Cumulative = CALCULATE(SUM(Table1[Value]),FILTER(ALL('Table1'),'Table1'[Date]<=MAX('Table1'[Date])),VALUES(Table1[NL]),VALUES(Table1[CC]),VALUES(Table1[Name]),VALUES(Table1[Project]),VALUES(Table1[Dept]))

Then create a matrix like below:

Best Regards,
Qiuyun Yu

[ImkeF · Accepted solution]

Just in case that your data is in the shape that you've shown, it's pretty easy to transform it into the shape of the solution above: In the query editor, you select the non-months columns, rightclick your mouse and choose: "Unpivot other columns":

[KSt]

Thank you ImkeF, unpivoting those columns was what needed to happen 🙂