Event count for a period of time

[TOBYLEUNG]

try to explain by excel, my table only have few item of event in a peroid of time,

then I would create an autocalendar and countif event on happen on this calender

=COUNTIFS(Sheet1!$D$2:$D$5,"<"&A2, Sheet1!$E$2:$E$5,">"&A2, Sheet1!$C$2:$C$5,"=TW")

How to do it by power BI, if i do it in excel the detail in power BI cannot interact

/* old message

how to count for event in a peroid of time

if i have 4 excel for different supplier

each have 3 to 6 events in last year

date = CALENDAR(ALL(dim_event[early date on schedule]),all(dim_event[latest date on schedule]))

 then i have 350 item in my date table as prefer

and 1 only have 15 item total form all of my excel, and all event have open and close date

we only count event between open and close 

how can i count for the opened event with the 350 days auto date?

                               

not the trend for every date

old message*/

[TOBYLEUNG · Accepted solution]

Just found the solution by indirect way and it works

Count = COUNTROWS(

FILTER(EventTable,

AND(EventTable[Location]="TW",

AND(EventTable[Open date]<Dates[Dates],

IF(EventTable[Close date]>0,

EventTable[Close date]>Dates[Dates],

1)

)

)

)

)

[TOBYLEUNG]

I just found this method will lost interact with the source table.

once i try to filter the event type, there still count for all, and can't find away to solve it yeah

[SpartaBI]

@TOBYLEUNG hey can you ping me tomorrow and we could do a quick zoom and look together if you want

[TOBYLEUNG]

what time do you prefer?

thank you very much

[SpartaBI]

Anytime between now - 7 hours and now -2 , just ping me here and I will find a time shortly after your ping me

[TOBYLEUNG]

well, could you help me now, had pm you

[TOBYLEUNG]

sorry i am confused for now -7 and now-2 means,

is this better to use Los Angeles, CA, USA TIME 19:30, and this will be 10:30 in my time zone,

so what is your prefer time period in LA time???

[SpartaBI]

Didn't undersrand 🙂 What is your time now? Just answer with your time when you reply

[TOBYLEUNG]

taiwan time zone

[SpartaBI]

@TOBYLEUNG are you free now?

[SpartaBI]

@TOBYLEUNG you can PM me to do a quick zoom to look together if you want