cancel
Showing results for
Did you mean:
Frequent Visitor

## YTD where months are column headings

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

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator

## Re: YTD where months are column headings

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

## Re: YTD where months are column headings

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":

Proud to be a Datanaut!

Imke Feldmann

3 REPLIES 3
Moderator

## Re: YTD where months are column headings

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

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User

## Re: YTD where months are column headings

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":

Proud to be a Datanaut!

Imke Feldmann

Frequent Visitor

## Re: YTD where months are column headings

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