YTD total with a measure

[Anonymous]

Hi everyone,

I am new here and learning power BI so I might be missing some obvious stuff but I cant figure out how to resolve my issue.

I am trying to create a TotalYTD value showing cashflow for the year. I created three other measures with MonthlyCashFlow showing me what I want

MonthlyCashFlow = [MonthlyIncome]-[MonthlyExpenses]

Now what I want to create is a running total of the year for my CashFlow so I tried this

CashflowFYTD = TOTALYTD(Sum([MonthlyCashFlow]),'Date'[Date])

but I get the error that SUM only accepts columns as an input. So this is where I am stuck

Thanks for any help

[Anonymous · Accepted solution]

It seems really stupid but the only way I got this to work was add the measure formulas in the TOTAL YTD Formula

MonthlyCashFlow = [MonthlyIncome]-[MonthlyExpenses]
MonthlyExpenses = SUMX(VALUES('Date'[FY&P]),SUM(OracleTransactions[Burden_Cost]))

So I used this and it worked

CashflowFYTD = TOTALYTD(SUM(Invoices[Invoice Total Amount]) - SUM(OracleTransactions[Burden_Cost]),'Date'[Date])

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Yes, you are correct!

I have similar solution with yours.

Create a calendar table and connect it with my table based on "date".

calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))

Create measures in my table

CashFlow = SUM(Sheet2[income])-SUM(Sheet2[expense])

ytd = TOTALYTD([CashFlow],'calendar'[Date])

Best Regards
Maggie

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

[Anonymous · Accepted solution]

It seems really stupid but the only way I got this to work was add the measure formulas in the TOTAL YTD Formula

MonthlyCashFlow = [MonthlyIncome]-[MonthlyExpenses]
MonthlyExpenses = SUMX(VALUES('Date'[FY&P]),SUM(OracleTransactions[Burden_Cost]))

So I used this and it worked

CashflowFYTD = TOTALYTD(SUM(Invoices[Invoice Total Amount]) - SUM(OracleTransactions[Burden_Cost]),'Date'[Date])