- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-24-2016 08:38 PM

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?

Solved! Go to Solution.

Accepted Solutions

## Re: Calculate a cumulative year to date and a avg month for year to date

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-25-2016 09:59 AM - edited 11-26-2016 12:36 AM

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)*

*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:*

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.

All Replies

## Re: Calculate a cumulative year to date and a avg month for year to date

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-25-2016 09:59 AM - edited 11-26-2016 12:36 AM

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)*

*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:*

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.

## Re: Calculate a cumulative year to date and a avg month for year to date

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-26-2016 08:44 PM

## Re: Calculate a cumulative year to date and a avg month for year to date

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-02-2016 09:12 AM

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

## Re: Calculate a cumulative year to date and a avg month for year to date

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-02-2016 10:00 AM

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.

## Re: Calculate a cumulative year to date and a avg month for year to date

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-02-2016 10:30 AM - edited 12-02-2016 07:45 PM

@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]) ))