cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

Event count for a period of time

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*/

1 ACCEPTED SOLUTION
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)
)
)
)
)
11 REPLIES 11
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)
)
)
)
)
Frequent Visitor

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

Community Champion

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

Frequent Visitor

what time do you prefer?

thank you very much

Community Champion

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

Frequent Visitor

well, could you help me now, had pm you

Frequent Visitor

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

Community Champion

Frequent Visitor

taiwan time zone

Community Champion

@TOBYLEUNG are you free now?

Community Champion

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

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors
Users online (909)