Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Vinutha
Frequent Visitor

Date Range with Date Slicer

Hello Power BI Experts,

 

I am currently working on defining a dashboard for which all the metric calculations are based on the Summaries(Date Range,Type-Monthly,Weekly,Yearly ) , Location and Department.

To start of with i started with the Date Range 

I created a Date table ,joined to the main table.Then in the main table i created a measure(indate Range) and dashboardcount. The count i see is more than what i see it manually

 

DATE TABLE SYNTAX:

Date =
CALENDAR(
    MIN('sheet'[start]),
    MAX('sheet'[end])
)
 
RECORD_IDSTART_DATEEND_DATE
166873391/15/24 8:50 PM1/17/24 7:11 PM
167090541/22/24 10:03 AM1/27/24 8:56 PM
166498771/5/24 6:03 AM1/6/24 5:32 PM
167074031/21/24 2:38 PM1/24/24 5:13 PM
167326601/28/24 3:06 AM1/31/24 6:13 PM
166851341/14/24 8:16 PM1/16/24 6:55 PM
166352761/1/24 3:24 PM1/3/24 11:04 AM
167337881/28/24 3:54 PM1/29/24 6:00 PM
166535431/5/24 10:33 PM1/6/24 11:04 AM
166355841/1/24 5:40 PM1/3/24 3:39 PM
166925791/17/24 12:24 PM1/17/24 9:05 PM
167000301/18/24 10:53 PM1/19/24 4:25 PM
166749201/11/24 3:59 PM1/13/24 9:02 PM
166815181/13/24 8:07 AM1/19/24 8:40 PM
167242611/25/24 4:22 PM1/27/24 10:54 AM
166348841/1/24 1:03 PM1/17/24 12:19 PM
166828161/13/24 6:26 PM1/14/24 6:48 PM
167035871/19/24 10:21 PM1/20/24 1:43 PM
166798341/12/24 4:57 PM1/12/24 11:14 PM
167307581/27/24 9:51 AM1/29/24 8:00 PM
166855851/15/24 3:58 AM1/19/24 6:52 PM
166958211/18/24 8:52 AM1/19/24 6:56 PM
166850861/14/24 7:46 PM1/15/24 3:20 PM
167377861/29/24 5:45 PM1/31/24 9:19 PM
166530491/5/24 6:52 PM1/7/24 11:50 AM
167253741/26/24 1:15 AM1/30/24 6:39 PM
166646871/7/24 10:36 PM1/9/24 6:00 AM
 
 
InDateRange =
VAR _rangeStart = MIN('Date'[Date])
VAR _rangeEnd = MAX('Date'[Date])
RETURN
IF (
    MAX('sheet'[start]) <= _rangeEnd &&
    MAX('sheet'[end]) >= _rangeStart,
    1,
    0
)
 
dashboardcount =
CALCULATE (
    COUNTROWS('sheet'),
    FILTER(
        'sheet',
        [InDateRange] = 1
    )
)
1 ACCEPTED SOLUTION

As you mentioned i changed the date format to exclude the time stamp by "many to one" and "both" and it worked. Thanks much for the lead.

View solution in original post

5 REPLIES 5
Vinutha
Frequent Visitor

dashboardcount =    COUNTROWS('PULSE DATA') Trying to get the count  and linked to the date range 
lbendlin
Super User
Super User

Calendar tables have Date columns that are actually DateTime type values pegged at midnight.  You will have to define what "Range"  means for you.  Is 1/15/24 8:50 PM counting as 1/15/24 or 1/16/24 ?  Or do you need the fractions too?

 

What is the expected outcome based on your sample data?

As you mentioned i changed the date format to exclude the time stamp by "many to one" and "both" and it worked. Thanks much for the lead.

Hello lbendlin,

 

Thank you so much for getting back to me. The expected outcome is count of the dashboard

What is the expected outcome based on your sample data?

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.