Filter out data after specific period of time

[cheid_4838]

I have a sql query where I am wanting to filter out "Do Not Invoice" order status seven days after the order completion date and leave all other statuses unchanged. I am fairly new to writing SQL and DAX queries. I have tried a couple of different ways to write the logic, but not having any luck.

Available 

Do Not Invoice 

Pending

Prepared

I have tried to incorporate the below logic into a CASE statement which I think is how it should be done, but not having any luck.

when DATEDIFF(DAY,[Completion Date], GETDATE() <= 7

Is there anyway to do this in SQL or in DAX?  Thanks.

[v-shex-msft]

Hi @cheid_4838,

Can you please share some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[cheid_4838]

I tried this logic, but keep getting NULL in my results.

CASE

WHEN [Invoice Status] IN ('CAN') THEN 

CASE WHEN DATEDIFF(DAY, [Completion Date], CAST(GETDATE() AS DATE)) <= 7 THEN 'CAN' ELSE [Invoice Status] END 

END as [InvoiceStatus]

,CASE

WHEN [Order Status] IN ('XIN') THEN

CASE WHEN DATEDIFF(DAY, [Completion Date], CAST(GETDATE() AS DATE)) <= 7 THEN 'XIN' ELSE [Order Status] END

END AS [OrderStatus]

[cheid_4838]

Here is some sample data.   What I want to see is all the "Do Not Invoice" status (bold) removed if the number of days is more than seven days from the current date.  I also want all other statuses to remain unchanged and included in the report.  Does this help.

Order Status	Completion Date	Days from Today's Date (6/25/24)
Available	1/24/2024	153
Do Not Invoice	1/4/2024	173
Pending	2/5/2024	141
Prepared	3/21/2024	96
Available	6/23/2024	2
Do Not Invoice	6/20/2024	5
Pending	6/17/2024	8
Prepared	6/1/2024	24
Available	4/24/2024	62
Do Not Invoice	4/1/2024	85
Pending	3/25/2024	92
Prepared	6/24/2024	1

[v-shex-msft]

HI @cheid_4838,

Here is the measure formula to check if the records is 'Do Not Invoice' status and over 7 days, you can use this on the 'visual level filter' to filter records:

Expired flag = 
VAR rowDate =
    MAX ( T1[Completion Date] )
VAR sysDate = TODAY ()
VAR _currStatus =
    SELECTEDVALUE ( T1[Order Status] )
RETURN
    IF (
        _currStatus = "Do Not Invoice"
            && DATEDIFF ( rowDate, sysDate, DAY ) >= 7,
        "Y",
        "N"
    )

Regards,

Xiaoxin Sheng