Forum Discussion

M4R10's avatar
M4R10
Frequent Visitor
9 years ago
Solved

Calculate last day with condition

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 audite...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago

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.

M4R10's avatar
M4R10
Frequent Visitor
9 years ago

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

  • v-sihou-msft's avatar
    v-sihou-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    M4R10

     

    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,