Reply
Highlighted
Frequent Visitor
Posts: 3
Registered: ‎11-24-2016
Accepted Solution

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


I am trying to get to the following output:


desiredoutput.PNG

 

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?


Accepted Solutions
New Contributor
Posts: 494
Registered: ‎04-21-2016

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

[ Edited ]

 

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

Screenshot 2016-11-26 00.47.39.png

 

 

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

Screenshot 2016-11-26 00.57.20.pngScreenshot 2016-11-26 00.57.34.pngThere is no Don't Summarize option, so i choose maximum in this caseThere 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.

View solution in original post


All Replies
New Contributor
Posts: 494
Registered: ‎04-21-2016

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

[ Edited ]

 

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

Screenshot 2016-11-26 00.47.39.png

 

 

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

Screenshot 2016-11-26 00.57.20.pngScreenshot 2016-11-26 00.57.34.pngThere is no Don't Summarize option, so i choose maximum in this caseThere 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.

Frequent Visitor
Posts: 3
Registered: ‎11-24-2016

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

Thank you for taking the time to answer this. I plan to try it out tomorrow and will let you know how it works.
New Contributor
Posts: 494
Registered: ‎04-21-2016

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

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

Frequent Visitor
Posts: 3
Registered: ‎11-24-2016

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

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.

New Contributor
Posts: 494
Registered: ‎04-21-2016

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

[ Edited ]

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

 

 

Screenshot 2016-12-03 01.47.57.pngScreenshot 2016-12-03 01.35.22.png