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
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.
Solved! Go to Solution.
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.
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! 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
33 | |
16 | |
13 | |
10 | |
8 |
User | Count |
---|---|
59 | |
20 | |
12 | |
11 | |
10 |