cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slash411 Frequent Visitor
Frequent Visitor

Need to create a monthly expense column and a running year to date column

I'm trying to create a comparative income statement. I've uploaded several data sets including a budget, the actual expenses for the year, and a date table. I've been able to create a matrix that shows a list of our accounts as well as a budget column for each account and an actual column for each account. I've added a slicer that can select any month out of the current year. What I need to do is, if someone slices on April, for example, create a column that would show the year to date totals (January - April). I'm pretty new to power BI and haven't learned many Dax functions yet so I appreciate any help!
8 REPLIES 8
sgsukumaran Member
Member

Re: Need to create a monthly expense column and a running year to date column

Do you have sample data?

slash411 Frequent Visitor
Frequent Visitor

Re: Need to create a monthly expense column and a running year to date column

Unfortunately our organization has blocked access to this community so I'm signed on in a different account from my phone with no access to the data to share. It's very basic tables that list account, amount, and month essentially.
sgsukumaran Member
Member

Re: Need to create a monthly expense column and a running year to date column

@slash411  

 

Create a measure based on month and slicer will work accordingly

 

Measure = TOTALYTD(
SUM('Table'[Amount]),
'Table'[Month]
)

slash411 Frequent Visitor
Frequent Visitor

Re: Need to create a monthly expense column and a running year to date column

Thanks. I actually tried this and it works as long as I haven't selected any slicers. Once I slice by a specific month it only gives me the total for that month.
sgsukumaran Member
Member

Re: Need to create a monthly expense column and a running year to date column

@slash411  - Try this

YTD= TOTALYTD(SUM('Table'[Amount]),'Table'[Month]
)

Measure=

var LMonth = MAX('Table'[Month]) //Last Month

return

if(min('Table'[Month])<=LMonth,[YTD])

slash411 Frequent Visitor
Frequent Visitor

Re: Need to create a monthly expense column and a running year to date column

Sorry - a little confused as I'm not sure what to do with the two formulas. I know I can create a new column or measure using one of the formulas but I'm not sure how I'm supposed to use both?
sgsukumaran Member
Member

Re: Need to create a monthly expense column and a running year to date column

BOth are measures. create the first obe (which you already have) and the second measure will call first measure

slash411 Frequent Visitor
Frequent Visitor

Re: Need to create a monthly expense column and a running year to date column

Oh I get it now. Unfortunately it's still behaving the same way. One more piece of info that I'm wondering might be throwing off something is that our fiscal year calendar runs from August 2018 through July 2019. So my date table is set up as :
Month. Date
August. 8/1/18
September. 9/1/18
October. 10/1/18
Etc.

I'm not sure if that would have an affect but trying to give as much info as possible. I appreciate you trying to help!

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors