Cash YTD vs prior YTD, month over month

[StaceyLGriffeth]

I'm hoping one of you brilliant minds can help me.  I am trying to compare Cash YTD, by month, compared to the same periods last year.  The x-axis is the Month name (Jan thru Dec), and there would be two lines:  2022 ending cash by month, and 2023 ending cash by month.  In my transactions, I have a beginning balance in January, then there are the actual transactions,  I used TotalYTD and get the correct running total, but only when I include the year in the axis.  And I've been completely unsuccessful getting the 2022 month ending totals.  Can anyone point me in the right direction?  Here is an example of how my transaction data is structured.  I also have a date table with a date field that is related to the transactions date field.

Trx Date	Amount	Description
1/1/2022	5000	Beginning Balance
1/1/2022	-655	Transaction
2/1/2022	783	Transaction
3/1/2022	1025	Transaction
4/1/2022	-2267	Transaction
5/1/2022	998	Transaction
6/1/2022	-143	Transaction
7/1/2022	1050	Transaction
8/1/2022	226	Transaction
9/1/2022	-775	Transaction
10/1/2022	-100	Transaction
11/1/2022	675	Transaction
12/1/2022	400	Transaction
1/1/2023	6217	Beginning Balance
1/1/2023	-770	Transaction
2/1/2023	-250	Transaction

Thanks!

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[StaceyLGriffeth]

@Ashish_Mathur how would I incorporate a third component into this?  It is currently prior year versus current year, but let's say we want to add the "Plan/Forecast" for the current year.  So for Jan/Feb there would be 3 figures, 2022 Actual, 2023 Actual, 2023 Plan, and March - December would have 2022 Actual and 2023 Plan.  We can assume the Plan figures would be in a separate table, but same format as the current Data table.

[Ashish_Mathur]

Not sure of what you want.  Share some eimple data and show the expected result in a simple Table format.

[StaceyLGriffeth]

Hi @Ashish_Mathur this is what I'm working towards:

One table has actuals, year over year, one table has budget.  The chart would be just like what you originally did with prior year and current year actuals overlayed Jan - Dec, but with a third line for current year budget.

[Ashish_Mathur]

Share some data to work with.

[StaceyLGriffeth]

Hello @Ashish_Mathur ,

Here is a pbix with sample data:  CashSample.pbix 

There are three tables:  CashTable which has transactions and should be calculated as running sum by calendar year (like in your example).  CashPlan is target for cumulative at end of each month.  Lastly is the DateTable.

I'd like to generate a bar/line chart with Jan - Dec as the x-axis.  Bars are for 2022 monthly running total, and 2023 (Jan/Feb) running totals.  Line would represent the amount for each month in CashPlan.

I apprecate your help!

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[StaceyLGriffeth]

Hello @Ashish_Mathur ,

I actually just got it to that same point, myself 🙂  The only issue now is that the current year YTD is not stopping with the latest data month.  You'll see how the February amount (latest data in the set) is showing for all remaining months?  I would want current year to show through February, but current year plan to show for all months.  Any suggestion how to make that happen?  I was able to do it without the plan because I had a flag for dates that were less than the max trx date which I applied as a filter to the graph.  I just can't work how to do it and still show the full year plan.

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[StaceyLGriffeth]

@Ashish_Mathur you're brilliant!  That is exactly what I need.  Thank you!

[Ashish_Mathur]

Thank you for your kind words.  Glad i could help.

[StaceyLGriffeth]

Hello @Ashish_Mathur I've been asked to modify the chart such that the bars represent current year YTD and current year Plan, and the line represents Prior YTD.  I'm struggling with how to make that work as we distinguished prior year/current year via the column legend.  I can get the appropriate values if I include the year on the x-axis, but the request is to display on the month name.  Do you have any suggestions?

[Ashish_Mathur · Accepted solution]

Hi,

This is the best i can do.  Download the PBI file from here.

[StaceyLGriffeth]

Thank you @Ashish_Mathur , that did it!

[Ashish_Mathur]

You are welcome.

[amitchandak]

@StaceyLGriffeth , try measure like these with help from date table

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

But if you want with without selection

YTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

LYTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = Date(Year(_max1)-1, Month(_max1), Day(_max1))
var _min = eomonth(_max,-1*MONTH(_max))+1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))