Trailing 12 week cash balance line graph

[NorthernGuy]

Hi,

I am accessing QuickBooks Online using the content pack and look to create a WEEKLY, trailing 12 week line graph representing the actual cash balance for each week ( 13 data points -- cash balance on the day of the report and then the cash balance for each of 12 weeks prior (todays date minus 7 days, 14 days, etc).  (Once/if I get this working, I will look to add bar chart with total cash deposits and total cash payments for that same 7 day period).  

What I attempted to do was use the Measures below to calculate the cash balance, this formula worked, but obviously, I can put these Measures into a line chart.  I included a Visual Filter on the specific Cash Account that is the subject of the graph.

T0W Cash = SUM(GeneralLedger[TxnAmount]

T1W Cash = CALCULATE(SUM(GeneralLedger[TxnAmount]), 'GeneralLedger'[Date] <= TODAY()-7)

T2W Cash = CALCULATE(SUM(GeneralLedger[TxnAmount]), 'GeneralLedger'[Date] <= TODAY()-14)

Day of the Report --> T0W

Trailing 1 Week --> T1W

Etc.

Suggestions?

[samdthompson]

Hi, @MattAllington is quite right. If you dont have a date table handy, here is a quick formula based one. Go to ribbon >> Modelling >> New Table

DateTable = ADDCOLUMNS (CALENDAR ( "01/01/2015","31/12/2017"),"Year", YEAR ( [Date] ),"Monthnumber", FORMAT ( [Date], "MM" ),"YearMonthShort", FORMAT ( [Date], "YYYY/MMM" ),"MonthNameLong", FORMAT ( [Date], "mmmm" ),"DayOfWeekNumber", WEEKDAY ( [Date] ),"DayOfWeek", FORMAT ( [Date], "dddd" ),"Quarter", "Q" & FORMAT ( [Date], "Q" ))

[MattAllington]

It's not clear to me what you are after. Do you want a rolling 12 week average, or do you want to see the last 12 weeks actual on a chart?  Or both?

[NorthernGuy]

Hi Matt,

Thanks for responding....

I want 13 data points, or amounts, representing the actual cash balance for the current day (the day the "user" generates the graph) and then the prior 12 weeks (7/14/21/etc days prior) which I can then chart on a line graph.  I'd like to chart these data points on a line graph with the X axis as the dates and then the Y axis as the amounts.

I think I understand how to calculate the data points, I just don't know how to associated them to a date on the X axis -- as a Measure is not associated to a date.

[MattAllington · Accepted solution]

I suggest you try to use the calendar table. Create a calculated column that creates a simple flag "last 13 weeks". Each week either is or isn't in the last 13. Then add a slicer (or filter ) and select "last 13 weeks". This will filter your calendar table. Then add a week column from the calendar table to your X axis, and write a single measure that does the calc.  This way you leverage the natural filtering behaviour to get what you need. 

[NorthernGuy]

Hi Matt (and Sam),

Thank you!  This works.  Once you see it, it's so much easier.

Appreciate the help.

Best.