Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Resolver II
Resolver II

Do you have sample data?

Anonymous
Not applicable

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.

@Anonymous  

 

Create a measure based on month and slicer will work accordingly

 

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

Anonymous
Not applicable

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.

@Anonymous  - Try this

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

Measure=

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

return

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

Anonymous
Not applicable

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?

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

Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.