Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Power_It_Up
Helper I
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 #OpenEnd

Networkdays

 

12301-Mar20-Mar14
45601-Feb05-Apr47
78901-Apr11-Apr9

 

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 #FebMarApr
123 14 
456214247
789  9
Avg212828

 

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

Power_It_Up_0-1713800168031.png

 

How can I make a DAX calc do this?

 

Many thanks in advance.

 

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

6 REPLIES 6
lbendlin
Super User
Super User

lbendlin_0-1713832271406.png

 

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

 

Power_It_Up_0-1713874022264.png

Many thanks again.

You mean show the actual value by month without cumulation?

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

Yes that's it. 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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