Exclude Covid time period from fact table without impacting time measures

[Anonymous]

Hello all, 

I'm looking for a solution to exclude the covid time period from the production history table I'm using to calculate frequencies of production (average and max). 

Basically what I have is a fact table filled with production orders over the past and from that I'm calculating the maximum period between two orders. It was so far working pretty good but the Covid period made some of my company plants closed for a few weeks. The direct consequence is that my measure becomes way too high because the maximum period between two orders becomes the covid stoppage...

Longuest period due to COVID

How can I exclude this period without getting wrong values due to long period comparison ? 

I've provided a sample file with the current logic (where you see that the answer is 104 while I'm expecting 29)

SAMPLE FILE (stored on my OneDrive) 

Let me know if that's clear and thanks in advance for your help. 

Kylo

[v-xuding-msft]

Hi @Anonymous ,

I add a if() into your fomula:

PREVIOUS ORDER DATE 1 = 
var previousDATE = MAXX (
    TOPN (
        1,
        FILTER (
           PROD_DATA,
         PROD_DATA[DATE] < EARLIER (PROD_DATA[DATE])
        ),
      PROD_DATA[DATE], DESC
    ),
   PROD_DATA[DATE]
)
RETURN
IF(MONTH(PROD_DATA[DATE]) IN {4,5,6},BLANK(),previousDATE)

[parry2k · Accepted solution]

@Anonymous you can add a flag in date table called "Covid" and then in your measure filter out that period where covid flag is true. I think this will be a more scalable solution.

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡