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.
Dear all,
I have a data set where:
Items are audited, reporting 0 or 1 as a result.
But not all items are audited every day, the frequency is random. So some days, where the item was not audited, the result is reported as NA (not available)
See an example below for 3 items since March 7th until March 19th:
Audit date | 07-03-2017 | 08-03-2017 | 09-03-2017 | 10-03-2017 | 11-03-2017 | 12-03-2017 | 13-03-2017 | 14-03-2017 | 15-03-2017 | 16-03-2017 | 17-03-2017 | 18-03-2017 | 19-03-2017 |
Item A | NA | NA | 0 | 0 | 1 | 1 | NA | 1 | 0 | NA | 0 | NA | NA |
Item B | 0 | 0 | 1 | 1 | NA | 1 | NA | 0 | NA | 0 | 0 | 0 | 0 |
Item C | 1 | 1 | 1 | 0 | 1 | 1 | 1 | NA | 1 | 0 | NA | 0 | 0 |
What I'm trying to create are 5 DAX formulas:
DAX FORMULAS | Last audit date | Previous audit to last audit date | last audited "0" date | last audited "1" date | Consequtive days audited "0" since last audited "0" date |
Item A | 17-03-2017 | 15-03-2017 | 17-03-2017 | 14-03-2017 | 1 |
Item B | 19-03-2017 | 18-03-2017 | 19-03-2017 | 12-03-2017 | 4 |
Item C | 19-03-2017 | 18-03-2017 | 19-03-2017 | 15-03-2017 | 2 |
Where,
1. Last audit date = It is the last date different to "NA"
2. Previous audit to last audit date = It is the penultimate date different to "NA"
3. Last audited "0" date = It is the last date equal to "0"
4. Last audited "1" date = It is the last date equal to "1"
5. Consequtive days audited "0" since last audited "0" date = How many consequtive days were "0" since the last day equal to "0"
Anyone could help me?
thanks in advance!
regards
Solved! Go to Solution.
In this scenario, you apply the filter to limit the table context into "Answer = 0", the measure will not return result when the current context is "Answer =1". You may apply IF condition to combine the "last audit 0" and "last audit 1" into one measure.
To calculate the consecutive appearance, please refer to my expression in following thread:
http://community.powerbi.com/t5/Desktop/Count-last-actual-consecutive-sorted-rows/m-p/149683
Regards,
First of all, I would unpivot your date columns to end up with something like:
Item,Audit date,Value
Item A,07-03-2017,NA
Item A,08-03-2017,NA
Item A,09-03-2017,0
...
At that point, most of your calculations should be relatively trivial along the lines of wrapping a MAX function for Audit date in a CALCULATE with an appropriate FILTER clause.
thanks @Greg_Deckler
Actually, my tables are normalized, I have two tables:
- VISITS to register visit dates
- AUDIT to register the answers of each visit
According to your suggestion, I created 3 new measures:
LAST AUDIT DATE = CALCULATE(MAX('TBL_VISITS'[AUDIT DATES]);
FILTER('TBL_AUDIT';'TBL_AUDIT'[ANSWER]>=0))
LAST 0 AUDIT = CALCULATE(MAX('TBL_VISITS'[AUDIT DATES]);
FILTER('TBL_AUDIT';'TBL_AUDIT'[ANSWER]=0))
LAST 1 AUDIT = CALCULATE(MAX('TBL_VISITS'[AUDIT DATES]);
FILTER('TBL_AUDIT';'TBL_AUDIT'[ANSWER]=1))
However I still have an issue, the results of "audit 0" and "audit 1" are reported ONLY when it was 0 or 1 respectively:
In the following sample, the column "Last 0 Audit" should report 13-12-2016 for the 2nd and 3rd row (03-01-2017 and 16-02-2017):
Any idea?
thank you!!!
In this scenario, you apply the filter to limit the table context into "Answer = 0", the measure will not return result when the current context is "Answer =1". You may apply IF condition to combine the "last audit 0" and "last audit 1" into one measure.
To calculate the consecutive appearance, please refer to my expression in following thread:
http://community.powerbi.com/t5/Desktop/Count-last-actual-consecutive-sorted-rows/m-p/149683
Regards,
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |