Forum Discussion

hnazzal's avatar
hnazzal
New Member
4 years ago
Solved

Get count between start and end date

Hello,

 

I have a list of projects, each project has an item, I need to calculate the count of items for all those projects START in specific Start Date OR Finish in specific finish date within same month.

 

 

Data Protection
  • Samarth_18's avatar
    Samarth_18
    4 years ago

    hnazzal Okay then you can try like below or you can share your PBIX file after removing sensitive data:-

    _Attendees =
var start_date = min(calender[date])
var end_date = max(calender[date])
return SUMX (
    FILTER (
        'Table (6)',
        OR (
            (
                MONTH ( 'Table (6)'[Start Date] ) = month(start_date)
                    && YEAR ( 'Table (6)'[Start Date] ) = year(start_date)
            ),
            (
                MONTH ( 'Table (6)'[End Date] ) = month(end_date)
                    && YEAR ( 'Table (6)'[End Date] ) = year(end_date)
            )
        )
    ),
    [Attendees count]
)

     

  • hnazzal's avatar
    hnazzal
    4 years ago

    Thank you, i will try this code

     

    But what do you mean by 

    [Attendees count]
     

     

11 Replies

  • Samarth_18's avatar
    Samarth_18
    Community Champion
    4 years ago

    Hi hnazzal ,

     

    It would helpful to answer more specifically if you could share sample data in text format with expected output.

    However you can try below code:-

    Measure =
CALCULATE (
    DISTINCTCOUNT ( table[items] ),
    FILTER ( table, MONTH ( table[start_date] ) = MONTH ( table[end_date] ) )
)

     

    Thanks,

    Samarth

  • hnazzal's avatar
    hnazzal
    New Member
    4 years ago

    Thank you for your reply!

     

    Let me explain my case exactly

     

    ProjectStart DateEnd DateAttendees count
    P11/12/20211/5/202210
    P25/5/202112/12/20217
    P312/7/202112/29/20213
    P46/6/20218/20/20216
    P512/5/20213/3/20214

     

    So, I need to calculate the count of attendees for the project either start with December 2021 OR End in December 2021 

    which in example 7 + 3 + 4 because these projects either start or end in December

     

    I create a measure as follows:

    TEST NEW Headcount = calculate(Sum(TEST[Name]),
Filter (
TEST,
TEST[Start_Date] <= calculate(max('calendar'[Date] )) &&
Test[End_Date] >= calculate(min('calendar'[Date]))))

     

    But still, it gives me the wrong dates.

     

    Thank you very much.

    • Samarth_18's avatar
      Samarth_18
      Community Champion
      4 years ago

      hnazzal , You can use below code:-

      _Attendees =
SUMX (
    FILTER (
        'Table (6)',
        OR (
            (
                MONTH ( 'Table (6)'[Start Date] ) = 12
                    && YEAR ( 'Table (6)'[Start Date] ) = 2021
            ),
            (
                MONTH ( 'Table (6)'[End Date] ) = 12
                    && YEAR ( 'Table (6)'[End Date] ) = 2021
            )
        )
    ),
    [Attendees count]
)

       

      Output:-

      • hnazzal's avatar
        hnazzal
        New Member
        4 years ago

        I tried this code, but unfortunately, it doesn't work

         

        BTW, the start date and end date will be dynamic, I mean to be in the slicer to allow users to pick the start and end date. 

         

        I created a calendar date to be user bu again it doesn't works with me 😞

         

        Thank you,

  • parry2k's avatar
    parry2k
    Super User
    4 years ago

    hnazzal answer to similar question was posted in this thread Solved: Date filter - Microsoft Power BI Community

     

     

    ✨ Follow us on LinkedIn and  to our YouTube channel

     

    Learn about conditional formatting at Microsoft Reactor

    My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

     

    ⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.