topic Filter out data after specific period of time in DAX Commands and Tips

Filter out data after specific period of time

cheid_4838 — Mon, 24 Jun 2024 18:35:00 GMT

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.

Re: Filter out data after specific period of time

Anonymous — Tue, 25 Jun 2024 01:15:25 GMT

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

Re: Filter out data after specific period of time

cheid_4838 — Tue, 25 Jun 2024 11:24:28 GMT

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

Re: Filter out data after specific period of time

cheid_4838 — Tue, 25 Jun 2024 12:26:49 GMT

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]

Re: Filter out data after specific period of time

Anonymous — Wed, 26 Jun 2024 02:18:15 GMT

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