Calculate a cumulative year to date and a avg month for year to date

[rwhitworth]

I am trying to get to the following output:

based on a list of amounts througout the month

Table 'Ledger'

Date           ActualDollars
1/3/2016      24
1/25/2016    15
1/31/2016    33
2/6/2016      15
2/10/2016    39
2/25/2016    30
3/1/2016       5
4/2/2016      10
4/20/2016    20
5/5/2016      15
5/15/2016    24
5/20/2016    30
6/1/2016      15
6/5/2016      30
6/23/2016    45
7/1/2016      28
8/5/2016      24
8/12/2016    16
9/1/2016      27
9/3/2016      18
9/5/2016       9
10/1/2016    12
10/17/2016  15
10/30/2016   24

I have a related date table with a full date like 'Ledger_GLDJ'[full_date]. The first column is easy as its just a sum of the individual charges in that month. I got the cumulative actual to work, even though I not sure that is the correct way to do it

Cumulative Actual = TOTALYTD(SUM(Ledger[ActualDollars]),Ledger[GLDGJ],FILTER(ALL(Ledger_GLDJ[full_date]),Ledger_GLDJ[full_date] <= MAX(Ledger_GLDJ[full_date])))

But I cannot figure out how to get the last column, average month (based only in the current year). In principle, this would be the Cumulative Actual for a given month / the month count. So in the first month, I would be dividing cumulative total by 1, in the second month I would be dividing the new cumulative total by 2, etc.

any one able to help on this?

[tringuyenminh92 · Accepted solution]

Create MonthInt: MonthInt = month(Ledger[Date].[Date])

Create Calculated Column: 

YTD - Total = Calculate(sum('Ledger'[ActualDollars]),FILTER(Ledger, Ledger[MonthInt]<=EARLIER(Ledger[MonthInt]) ) )

As my observation, the DAX above will generate same accumulative total result in each month

so you need to choose Maximum or Minimum or Average in the Fields properties to visualize:

(i will choose maximum)

You could choose Don't summarize or maximum or minimum or average, the visualize result will be sameYou could choose maximum or minimum or average, the visualize result will be same

Create Calculated Column by divided for distinctcount of month : 

YTD - Avg = Calculate(SUM(Ledger[ActualDollars])/DISTINCTCOUNT(Ledger[MonthInt]),FILTER(Ledger, Ledger[MonthInt]<=EARLIER(Ledger[MonthInt]) ) )

And choose Don't summarize for that YTD - AVG value:

There is no Don't Summarize option, so i choose maximum in this case

 Please notice that if you add filter/slicer by Date level, when you select filters the result could be wrong in this scenario.
Or you could summarize(groupby) your data to another calculated table to ensure month is the lowest level of group by, then use the first expression.

[rwhitworth]

Thank you for taking the time to answer this. I plan to try it out tomorrow and will let you know how it works.

[tringuyenminh92]

@rwhitworth Hope this approach help to solve your problem. please kindly mark this as solution if it's working for your case.

[rwhitworth]

i will mark it correct. you answered the question I asked. However, I have determined I have a new problem, I simplified the data set too much for the example. I actually need cumulative and monthly average by month AND by category. not sure how to do this.

[tringuyenminh92]

@rwhitworth In that case, you need to use Calculated Measure in another approach (sorry for above solution cause it's my favorite solution)

• Create a date table -> Go to Modelling click New Table -> enter Dates = CALENDARAUTO()
• Create Relantionship between your Ledger[Date] and Dates[Date] ( the calculated table)
• Ensure your data has all 12 months (Nov and Dec should have 0 instead of no data)

Create measure:

Cumulative Actual = CALCULATE(sum(Ledger[ActualDollars]),FILTER(ALL(Dates[Date]),Dates[Date]<=MAX(Dates[Date]) ))

Avg Actual = CALCULATE(sum(Ledger[ActualDollars])/DISTINCTCOUNT(Ledger[Month]),FILTER(ALL(Dates[Date]),Dates[Date]<=MAX(Dates[Date]) ))