Average of Open>End Date - MoM

[Power_It_Up]

Hi.

I cannot attach a workbook as yet, so I will have to use visuals.

Example data:-

Case #	Open	End	Networkdays
123	01-Mar	20-Mar	14
456	01-Feb	05-Apr	47
789	01-Apr	11-Apr	9

By month, I want to calculate a "no of days average" of all cases that fall between OPEN and END dates.  For example, the below table shows a breakdown of the numbers:-

Case #	Feb	Mar	Apr
123		14
456	21	42	47
789			9
Avg	21	28	28

Let's take case #456.  It was opened on 01-Feb and closed 05-Apr.  Therefore, the calcs are as follows:-

For FEB: 01-Feb to 29-Feb = 21 days

For MAR: 01-Feb to 31-Mar = 42 days

For APR: 01-Feb to 05-Apr (End Date) = 47

I would then average the sum of all numbers by month to give me this graph:-

How can I make a DAX calc do this?

Many thanks in advance.

[lbendlin · Accepted solution]

[Power_It_Up]

Hi again @lbendlin .

Is there a way in which we can show the difference from the previous month, rather than cumulative?, i.e. 21, 21, 17

Many thanks again.

[lbendlin]

You mean show the actual value by month without cumulation?

[Power_It_Up]

@lbendlin I actually found the solution.  I enabled Visual Calculations and added the "Versus Previous" function to the bar chart using your super calculation!  Many thanks again. 🙂

EDIT: Actually that "kind of" works, but not quite.  Back to the drawing board.  The "New Calculation" option in Bi is similar to the "Add Table Calculation" in Tableau, so learnt something new there.

[Power_It_Up]

Yes that's it. 

[Power_It_Up]

Hi @lbendlin .  Thank you so much for this.  Your solution worked a treat for my dashboard purposes!  🙂