Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
i want to create a measure to arrive at one number as how much is due in the month, I wrote teh below calculation in column, however this is not helping me achive what i want.
How does this work in a measure?
Over due in the month = IF((' Audits'[Compliance Date]< ' Audits'[Date Audit Performed])|| ISBLANK(' Audits'[Date Audit Performed]) &&'SAM Supplier Audits'[Compliance Date]< TODAY(),1,0)
Also want to add an additional condition of Compliance date is No Null...
I want to further calculate % Completed on time...using this as one of input..
Thanks a lot!!
Solved! Go to Solution.
@JCK2 - Yep!
Hi @JCK2
My solution below is to calculate %completed based on "Compliance date" every month.
Create a date table
date = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))
Then create a column
over due =
IF (
(
[Compliance Date] < [Date Audit Performed]
|| ISBLANK ( [Date Audit Performed] )
)
&& (
[Compliance Date]
<> BLANK ()
&& [Compliance Date]
< TODAY ()
),
1,
0
)
create measures
count of completed =
CALCULATE (
COUNT ( 'Table'[Compliance Date] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[over due] = 1
),
USERELATIONSHIP ( 'date'[Date], 'Table'[Compliance Date] )
)
countall = CALCULATE(COUNT('Table'[Compliance Date]),USERELATIONSHIP('date'[Date],'Table'[Compliance Date]))
% = [count of completed]/[countall]
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Maggie, this is great, which means i can possible use that new data as a slicer for the users.
For count of completed, instead of compliance date, i should be using date performed.
I will let you know, how it is going to work out..
Maggie, can i use this new table as a master date slicer; means connecting multiple date to this date table. I have other things that is shown in the dashboard. ( No# compliance, No# Actions etc..). So can i join the initiation date of all (Compliance, Action) and create a join with this new year table? and use them as a master date slicer on the dashboard...
Thanks a lot!
First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Second, maybe:
Over due in the month = IF((MAX(' Audits'[Compliance Date])< MAX(' Audits'[Date Audit Performed]))|| ISBLANK(MAX(' Audits'[Date Audit Performed])) && MAX('SAM Supplier Audits'[Compliance Date])< TODAY(),1,0)
Thanks a lot Greg!! I am going to try this.
Greg, i used the calculation and when i dragged it into a table it works, but when i want to get a total, it does not seem to work
For eg: by month or by year, like an aggregated count, it does not seem to work. I think its because we are using the max calculation and what it does is calculate the over due on the latest date avaialable and showing the result as zero and not showing a count of all over dues.
@Greg_Deckler I have now been able to create 2 columns
a) Audits Due
b) Audits Over due
Now i just want to create a calculation by creating a column
Audit Due-Overdue/Audits due to get the % completed on time.
However I am getting a circular dependcy error 😞
@JCK2 OK, trying to come back up to speed on this, what are your formulas for the three columns?
These are the calculations i am using...
Audits due column = CALCULATE(DISTINCTCOUNT(' Audits'[PRID]),VALUES(' Audits'[Compliance Date]))
Audits Over due Column = IF((' Audits'[Compliance Date]< ' Audits'[Date Audit Performed])|| ISBLANK(' Audits'[Date Audit Performed]) && (' Audits'[Compliance Date])< TODAY(),1,0)
Thanks for checking Greg!!
OK, well here is your circular dependency when you use these together:
Audits due column = CALCULATE(DISTINCTCOUNT(' Audits'[PRID]),VALUES(' Audits'[Compliance Date]))
Audits Over due Column = IF((' Audits'[Compliance Date]< ' Audits'[Date Audit Performed]) || ISBLANK(' Audits'[Date Audit Performed]) && (' Audits'[Compliance Date])< TODAY(),1,0)
So, I don't understand that if the 2 things above are columns, why are you using VALUES? Are these measures or columns in a table?
Really, some SAMPLE data would be incredibly useful. Just make some up that emulates your actual data. Would only need 2 or 3 rows.
@Greg_Deckler , I've created a sample data set, also tried to explain as i can!
Hope its clear, please let me know if you need more info! Thanks a ton 🙂
Sorry here is the link to https://www.dropbox.com/sh/w1cxxiijhn7060u/AACtywXqUK00HuAKWRP2vstYa?dl=0
Id | Site | Compliance Date | Date Performed | Total Due | Total Overdue | % performed on time |
1 | Amsterdam | 10/03/2020 | 09/03/2020 | 1 | 0 | 100.00% |
2 | Utrecht | 12/03/2020 | 10/03/2020 | 1 | 0 | 100.00% |
3 | Venlo | 12/03/2020 | 10/03/2020 | 1 | 0 | 100.00% |
4 | Amsterdam | 13/03/2020 | 15/03/2020 | 1 | 1 | 0.00% |
5 | Lellyan | 13/03/2020 | 1 | 1 ( over due because not performed) | 0.00% | |
6 | Milan | 17/03/2020 | 17/03/2020 | 1 | 0 | 100.00% |
7 | Rome | 12/03/2020 | Not counted because there is no complaince date | #VALUE! | ||
8 | Spain | 13/03/2020 | 14/03/2020 | 1 | 0 | 100.00% |
9 | Utrecht | 13/03/2020 | 15/03/2020 | 1 | 1 | 0.00% |
10 | Amsterdam | 15/03/2020 | 15/03/2020 | 1 | 0 | 100.00% |
11 | Lisbon | 15/03/2020 | 15/03/2020 | 1 | 0 | 100.00% |
12 | Milan | 22/03/2020 | 22/03/2020 | Not counted because the date is in future | #VALUE! | |
Total | 10 | 3 | 70.00% | |||
Total Due: Countdistint of id where compliance date is non blank and less than today (ID is unique and there can be multiple audits on same date), so need to take id into consideration for total due | ||||||
OK, I did it with these two columns:
Total Due 1 = IF(ISBLANK([Compliance Date]) || [Compliance Date] > TODAY(),BLANK(),1)
Total Overdue 1 =
SWITCH(TRUE(),
ISBLANK([Compliance Date]),BLANK(),
ISBLANK([Date Performed]) && [Compliance Date] < TODAY(),1,
[Date Performed] > [Compliance Date],1,
[Compliance Date]>TODAY(),BLANK(),
0
)
And this measure:
Measure =
DIVIDE(COUNTROWS(FILTER('Table',NOT(ISBLANK([Total Overdue 1])) && [Total Overdue 1] = 0)),SUM('Table'[Total Due 1]))
PBIX is attached. I think you have an error in your data for Spain. Spain looks like it is overdue.
Thanks a lot Greg!! Yes you are right...Spain should be over due....!
For the total due, instead of counting it as 1, if we use distinct count of the id's, we are there...currently this is the result..because 1 audit can be done at two places with same id and we want to calculate it as 1.
i did tried calculation to replace 1 by disntict count of ID's but no luck...:( still see the same numbers..
@JCK2 - are you looking for this?
Measure 2 =
DIVIDE(
COUNTROWS(FILTER(FILTER('Table',DISTINCT('Table'[Id])),NOT(ISBLANK([Total Overdue 1])) && [Total Overdue 1] = 0)),
SUM('Table'[Total Due 1])
)
Apologies, maybe this:
Measure 2 =
DIVIDE(
COUNTROWS(FILTER(SUMMARIZE('Table',[Id],"__Count",IF(NOT(ISBLANK(MAX('Table'[Total Overdue 1]))) && MAX([Total Overdue 1]) = 0,1,0)),[__Count]=1)),
SUM('Table'[Total Due 1])
)
Greg, do you have a pbx file? Thanks
@JCK2 - Yep!
@Greg_Deckler Thanks a lot for your effort and patience, it worked :)!
I started using Powerbi from last week, always have been using Alteryx and Tableau...this was my first challange!!
Appreciate your help in onboarding and making me love PowerBi!!
Look forward to keep learning more things from you Greg!! You are a True Guru :)! Thanks once again!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |