topic Re: Event count for a period of time in DAX Commands and Tips

Event count for a period of time

Anonymous — Mon, 25 Jul 2022 03:03:00 GMT

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

Re: Event count for a period of time

SpartaBI — Sun, 24 Jul 2022 08:38:00 GMT

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

Re: Event count for a period of time

Anonymous — Mon, 25 Jul 2022 04:00:48 GMT

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],

)

Re: Event count for a period of time

Anonymous — Thu, 28 Jul 2022 15:36:46 GMT

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

Re: Event count for a period of time

SpartaBI — Thu, 28 Jul 2022 15:57:41 GMT

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

Re: Event count for a period of time

Anonymous — Thu, 28 Jul 2022 16:12:26 GMT

what time do you prefer?

thank you very much

Re: Event count for a period of time

SpartaBI — Thu, 28 Jul 2022 16:26:54 GMT

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

Re: Event count for a period of time

Anonymous — Thu, 28 Jul 2022 16:33:09 GMT

well, could you help me now, had pm you

Re: Event count for a period of time

Anonymous — Thu, 28 Jul 2022 17:05:45 GMT

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

Re: Event count for a period of time

SpartaBI — Thu, 28 Jul 2022 17:51:45 GMT

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

Re: Event count for a period of time

Anonymous — Thu, 28 Jul 2022 22:27:35 GMT

taiwan time zone

Re: Event count for a period of time

SpartaBI — Fri, 29 Jul 2022 06:46:37 GMT

Anonymous are you free now?