Measuring If something has gone past a certain date

[Anonymous]

Hi,

I have a table from and SQL Database that tells me a document number, a Scan By Date and a date that it was actaully scanned. 

How can I measure how many documents where scanned 24, 48 and 72 hours after the Scanned by Date.

I Have a conditional Column in that says if the Scan Date equals the Scan By Date then Yes which works fine and i can calculate a percentage of it. However when i try and write a measure;

Scanned 24 hrs After Scan By Date = If(Table1[ScanDate]>Table1[ScanByDate]+24/60/24,"Yes","No"  it doesn't seem to work. 

Anyone have any Suggestions?

Thanks In Advance. 

[v-yulgu-msft · Accepted solution]

Hi @Anonymous ,

For measures, you should modify the formula as:

Scanned 24 hrs After Scan By Date =
IF (
    DATEDIFF (
        SELECTEDVALUE ( Table1[ScanDate] ),
        SELECTEDVALUE ( Table1[ScanByDate] ),
        HOUR
    ) > 24,
    "Yes",
    "No"
)

Then, to count how many documents were scanned 24 hours after the Scanned by Date, you can create such a measure:

Count24hours =
CALCULATE (
    COUNT ( Table[document number] ),
    FILTER ( ALLSELECTED ( Table ), [Scanned 24 hrs After Scan By Date] = "Yes" )
)

Best regards,

Yuliana Gu

[AlB]

Hi @Anonymous

If ScanDate and ScanByDate are if type date (I guess so), you can get your measures with something like:

Scanned 24 hrs After Scan By Date =
COUNTROWS ( FILTER ( Table1, Table1[ScanDate] > Table1[ScanByDate] + 1 ) )

Scanned 48 hrs After Scan By Date =
COUNTROWS ( FILTER ( Table1, Table1[ScanDate] > Table1[ScanByDate] + 2 ) )

Same logic for 72 hours. Place the measures in Card visuals for instance. 

If you want the percentage for example of the 24h ones, you can use the previous measures:

% of Scanned 24 hrs After Scan By Date =
DIVIDE ( [Scanned 24 hrs After Scan By Date], COUNTROWS ( Table1 ) )

One question I'm curious about. What is the 24/60/24 for in the code you show?

On a different note, for your posts in general, it's best to show a sample of your dataset in text-tabular format and explain with an example based on that data what the issue is. That allows people trying to help to readily copy the data and increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally for more complex cases, share the pbix (beware of confidential data).  

[Anonymous]

Hi @AlB thanks for looking at this!

I have tried the measure below but it brings up "(Blank)".

The 24/60/24 is something i have used in excel before, for example if(iferror(Actaul Time - Planned Time,0)<46/60/24,"On Time","Late") a formula to establish on time or late over 45 minutes. It is to establish Date, so if i was planned to collect at 23:30 and actaully collected at 01:30, i would be late, but normal it would say i was on time because excel would think it was the same day.