cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KSt Frequent Visitor
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.

 

NLCCNameProjectDeptApr-16May-16Jun-16Jul-16Aug-16Sep-16etc
40001FundraisingCLSVol100010001000100010001000 
40011FinanceCLSAcc850 600 916  
40022SalesMTAcc500500500500500500 
40034TelephoneBRInt100  100   

 

 Thank you for your help

2 ACCEPTED SOLUTIONS

Accepted Solutions
Moderator v-qiuyu-msft
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:

 

q4.PNG

 

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:

 

q6.PNGq5.PNG


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

 

PBI_YTDWhereMonthsAreColumnHeadings.png

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




3 REPLIES 3
Moderator v-qiuyu-msft
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:

 

q4.PNG

 

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:

 

q6.PNGq5.PNG


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

 

PBI_YTDWhereMonthsAreColumnHeadings.png

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Imke Feldmann

How to integrate M-code into your solution -- Check out more PBI- learning resources here -- Performance Tipps for M-queries




KSt Frequent Visitor
Frequent Visitor

Re: YTD where months are column headings

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