Date Slicer

[Anonymous]

Hi, 

How to add date slicer and get dynamic count of ID's?

tbl1

ID       timestamp                               

1           28/09/2020 11:14              

1           29/09/2020 11:14

1          30/09/2020 11:14 

1          1/10/2020 11:14

1           3/10/2020 11:14

1          5/10/2020 11:14

tab2:

 Used  DAX formulas to calculate min and max:

ID             min_date         Max_date           TotalLogin

1                28/09/2020     05/10/2020           6

Using date slicer if I select min_date =3/10/2020 and max_date = 5/10/2020 . My Total login should be 2.

The total logins are not count of dates but count of Id's.

Time can be ignored for now.

Thank you for helping with DAX.

[v-jingzhang · Accepted solution]

Hello @md264092 , is Table 2 your expected result? If so, you can follow the steps below for the reference.

First, add a new Date column to Table 1.

Date = Tbl1[timestamp]. [Date]

Next, create the following measures:

Max_date = MAX(Tbl1[Date])

Min_date = MIN(Tbl1[Date])

TotalLogin = COUNT(Tbl1[ID])

Finally, place the Date column in a slicer, place IDs, and three previous measures in a table visual. Now, when you change the maximum and minimum date in the slicer, the data in the table will change accordingly.

Best regards

Community Support Team _ Jing Zhang

If this post helps,please consider Accepting it as the solution to help other members find it.

[Anonymous]

Thank you Jing Zhang for your help.

It worked well!

[amitchandak]

@Anonymous ,

Create a date colum

Date = [timestamp].date

PNow you can this in slicer or join with date table use that in slicer.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.