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 sam...
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]
     

     

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,

    • Samarth_18's avatar
      Samarth_18
      Community Champion
      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
        New Member
        4 years ago

        Thank you, i will try this code

         

        But what do you mean by 

        [Attendees count]