Counting of dates

[tarun912]

I have a CREATEDTIMESTAMP column in my table and I will enter a date in my UI (Power bi dashboard) and my requirements will be :

- I need to count all the dates (basically the count of dates and some dates are repeating also , and we will have to count that also) that are there in the CREATEDTIMESTAMP table 1 week previous to the user entered date.

For ex: 
If a user entered [16-DEC-19], 
then i need to count the dates from [08-DEC-19] to [15-DEC-19]     (8 included and 15 excluded)

08-DEC-19 04.46.17.220000000 AM
08-DEC-19 04.46.18.201000000 AM
08-DEC-19 06.35.24.291000000 PM
09-DEC-19 07.00.39.784000000 PM
10-DEC-19 07.00.41.774000000 PM
11-DEC-19 07.00.42.779000000 PM
12-DEC-19 07.00.43.778000000 PM
12-DEC-19 07.00.44.774000000 PM
13-DEC-19 07.00.45.785000000 PM
13-DEC-19 07.00.46.774000000 PM
13-DEC-19 07.00.49.775000000 PM
14-DEC-19 07.00.50.777000000 PM
14-DEC-19 07.00.51.777000000 PM
14-DEC-19 07.00.52.779000000 PM
14-DEC-19 07.00.53.778000000 PM
14-DEC-19 07.47.41.803000000 AM
14-DEC-19 05.00.00.452000000 PM
14-DEC-19 10.36.58.449000000 AM
14-DEC-19 08.18.47.963000000 PM
14-DEC-19 10.25.49.492000000 PM
14-DEC-19 10.25.50.475000000 PM

Now, my result should be 21 .

[Anonymous]

If a user entered [18-DEC-19], it also counts the dates from [08-DEC-19] to [15-DEC-19], is that right?

[tarun912]

Hi rhlol, 

If a user entered [16-DEC-19]* , it also counts the dates from [08-DEC-19] to [15-DEC-19], is that right?
*correction in the date.

Basically, we need to count the 1 week prior dates 
Hope to hear from you soon.

Thanks !

[Ashish_Mathur]

Hi,

In the Query Editor, seperate the Date portion from the Date/time stamp and rename the column as Date.  Create a Calendar Table and build a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  Create a slicer from the Date column of the Calendar Table and select any date there.  Write this measure

=CALCULATE(COUNTROWS(Data),DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-8,MIN(Calendar[Date])-1))

Hope this helps.

[tarun912]

Hi, 

I tried the solution that you gave me, but it is not working
Can you please help ?

Hope to hear from you soon.
Thanks !

[Anonymous]

Hi, you may also try to add a calculated column to change the datetimestamp to date value, and follow Ashish's suggestion.

DATEVALUE( LEFT( Table1[datetimestamp], 9))

[Ashish_Mathur]

Share the link from where i can download your file.