cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TOBYLEUNG
Frequent Visitor

Event count for a period of time

TOBYLEUNG_0-1658717948331.pngTOBYLEUNG_1-1658717993411.png

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?

TOBYLEUNG_0-1658646343864.png                               TOBYLEUNG_1-1658646393447.png

not the trend for every date

old message*/

 

 

1 ACCEPTED SOLUTION
TOBYLEUNG
Frequent Visitor

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)
)
)
)
)

View solution in original post

11 REPLIES 11
TOBYLEUNG
Frequent Visitor

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)
)
)
)
)

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

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

what time do you prefer?

thank you very much

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

well, could you help me now, had pm you

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???

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

taiwan time zone

@TOBYLEUNG are you free now?

SpartaBI
Community Champion
Community Champion

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

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors