Filter value before a number of days

[Anonymous]

Hi,

I have a table below : 

Name	Date
Name 1	01/02/2019
Name 2	02/02/2019
Name 3	03/02/2019
Name 4	04/02/2019
Name 5	05/02/2019
Name 6	06/02/2019
Name 7	07/02/2019
Name 8	08/02/2019
Name 9	09/02/2019
Name 10	01/02/2023
Name 11	02/03/2023
Name 12	03/03/2023
Name 13	04/03/2023
Name 14	05/03/2023
Name 15	06/03/2023
Name 16	07/03/2023
Name 17	08/03/2023
Name 18	09/03/2023
Name 19	10/03/2023
Name 20	11/03/2023

I would like to count the total name before 20 days (today 16/03/2023). 

The result should be  :  10

Name	Date
Name 1	01/02/2019
Name 2	02/02/2019
Name 3	03/02/2019
Name 4	04/02/2019
Name 5	05/02/2019
Name 6	06/02/2019
Name 7	07/02/2019
Name 8	08/02/2019
Name 9	09/02/2019
Name 10	01/02/2023

Thank you in advance.

[KeyurPatel14]

Hi @Anonymous ,
I hope you only want the count of names as you mentioned in the post so according to that I have implemented the same scenario like your and got the expected output.
So you have to perform below steps in order to solve your peoblem.

I have created one measure named M2 as you can see in the image.

I have named my table as Table3 so dont be confused.
I have plotted the above measure M2 in the card visual as you can see in the image.

As today's date is 17/03/2023 so the date before 20 days from today is 26/02/2023 as you said in the reply.

I hope this will solve your probem and still if you have any queries then please let me know.
And if this helps you then please give it kudos and mark it as a solution.
Thank you. 
So as per the data in the table you can see we have 11 records before 26/02/2023 as the date for Name 12 is 03/03/2023 so it will not be counted according to the logic so the count should be 11 and I got the result.

[Anonymous]

Hi,

thank you for your help . 

I got the following error when applies the dax to my dataset : 

Could you please help 

MdxScript(Model) (347, 19) Calculation error in measure 'Measure'[A]: A date column containing duplicate dates was specified in the function call 'DATESBETWEEN'. This is not supported.

[MohammadLoran25]

Hi,

Not Clear. What do you mean by before 20 days?

[Anonymous]

I meant before the last 20 days (>20 days). For example : today is 17 Mar, I want to extract the data of (17 Mar ) minus 20 days. It means the data before 26 Feb

[MohammadLoran25]

Okay. Assume you have a separate date table which has relationship with your Name and Date Table that is provided above.

You need to create a measure as below to achieve what you want:

CALCULATE (
    COUNTROWS( VALUES( TABLE[NAME] )),
    FILTER ( ALL ( DateTable ), DateTable[Date] <= TODAY () - 20 )
)

If it solves your problem, then please consider Accepting it as the solution
Regards,
Loran

[Anonymous]

Hi, 

It doesn't work. 😞

[KeyurPatel14]

HI @Anonymous ,
It is working as expected on sample data as I shared images with you.
Please share the exact problem and share the pbix if you can.
Thank you.

[MohammadLoran25]

Why? I checked it on a sample data and it works.

If you can, please share your power bi file (a sample would be great as well if you have sensitive data)

[KeyurPatel14]

HI @Anonymous ,
You only want the count of records right?