Trying to get a Gauge of Sales vs Average Daily Expenses

[Anonymous]

Hi All,

I have a table of Payments from Quickbooks that represents all expenses for my business.

On my daily (/weekly/monthly) status page I have my daily income, pulled from Square.

What I'd like to have is a gauge that shows how income is doing relative to expenses (was the day profitable?)

To that end, I'd like the target value for the gauge to be the average daily expenses calculated from the last 30 days.

I believe the two relevant columns are:

Total_Amount

Transaction_Date

How do I get the average daily expense into the gauge?

[v-lili6-msft]

hi, @Anonymous 

You could create two measure and use them in Gauge:

last 30 day rolling average = 

	VAR __LAST_DATE = LASTDATE('Table'[Transaction_Date])
	RETURN
		AVERAGEX(
			DATESBETWEEN('Table'[Transaction_Date], DATEADD(__LAST_DATE, -30, DAY), __LAST_DATE),
			CALCULATE(SUM('Table'[Total_Amount])))

Current value = CALCULATE(SUM('Table'[Total_Amount]),LASTDATE('Table'[Transaction_Date]))

If not your case, please share your simple sample data and expected output.

Best Regards,

Lin