Forum Discussion

Vinutha's avatar
Vinutha
Frequent Visitor
1 year ago
Solved

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
    )
)
Need Help
  • Vinutha's avatar
    Vinutha
    1 year ago

    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.

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    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?

    • Vinutha's avatar
      Vinutha
      Frequent Visitor
      1 year ago

      Hello lbendlin,

       

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

      • lbendlin's avatar
        lbendlin
        Super User
        1 year ago

        What is the expected outcome based on your sample data?

    • Vinutha's avatar
      Vinutha
      Frequent Visitor
      1 year ago

      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.

  • Vinutha's avatar
    Vinutha
    Frequent Visitor
    1 year ago
    dashboardcount =    COUNTROWS('PULSE DATA') Trying to get the count  and linked to the date range