cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Average of Open>End Date - MoM

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?

1 ACCEPTED SOLUTION
Super User
6 REPLIES 6
Super User

Helper I

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.

Super User

You mean show the actual value by month without cumulation?

Helper I

@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.

Helper I

Yes that's it.

Helper I

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

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors