Occurrence per week which should be filterable by month

[naveenwills]

With a Master table as below

How to caculate how many times a cardholder has appeared in a week using DAX?

for example I want the end result to look like below and this should be filterable by month, if I change to January the week numbers should change with it as well. I have a seperate calander table. 

Another important thing is the calculation should consider only one entry per day that means if the cardhoder goes out and in several times in a day, it should only calculate once per day. So the occurance can only be once, twice, trice, 4 times, 5 times, 6 times or all 7 days. 

I have managed to do this using power query grouping. However I want to be able to do this on the master table in DAX.

any help is greatly appreciated.

Thanks

[naveenwills]

Thanks @v-huijiey-msft , 

That doesnt work as it sets 0 to a person who has only 1 occurrence. 

See sample data 

Entry Date/Time       Location    Cardholder Name Email Address     Week of Year

08/01/2024 08:02	Loc1	Mr D Duck	d.duck@gomail.com	2
08/01/2024 14:21	Loc1	Mr D Duck	d.duck@gomail.com	2
09/01/2024 08:56	Loc1	Mr D Duck	d.duck@gomail.com	2
11/01/2024 08:13	Loc2	Mr D Duck	d.duck@gomail.com	2
12/01/2024 08:31	Loc1	Mr D Duck	d.duck@gomail.com	2
15/01/2024 08:08	Loc1	Mr D Duck	d.duck@gomail.com	3
15/01/2024 09:43	Loc1	Mr J Bloggs	jbloggs@gomail.com	3
16/01/2024 08:24	Loc1	Mr D Duck	d.duck@gomail.com	3
16/01/2024 10:27	Loc2	Mr D Duck	d.duck@gomail.com	3
17/01/2024 14:17	Loc2	Mr D Duck	d.duck@gomail.com	3
18/01/2024 12:40	Loc1	Mr D Duck	d.duck@gomail.com	3
19/01/2024 08:07	Loc1	Mr D Duck	d.duck@gomail.com	3
22/01/2024 08:35	Loc2	Mr D Duck	d.duck@gomail.com	4
22/01/2024 11:59	Loc1	Mr D Duck	d.duck@gomail.com	4
23/01/2024 08:16	Loc1	Mr D Duck	d.duck@gomail.com	4
25/01/2024 00:00	Loc1	Mr D Duck	d.duck@gomail.com	4
26/01/2024 08:08	Loc1	Mr D Duck	d.duck@gomail.com	4

Result I want to see

	Mr D Duck	Mr J Bloggs
Week 2	4
Week 3	5	1
Week 4	4

[v-huijiey-msft · Accepted solution]

Hi @naveenwills ,

In the Power Query, select the Transform tab and click Group By to make the following settings.

This will calculate the number of occurrences per week for each cardholder.

Click Close and Apply to return to Power BI Desktop.

Select the Matrix visualization.

Drag Week of Year to the Rows field.

Drag Cardholder Name to the Column field.

Drag Count to the Values field and set the aggregation to "Count".

The final visual is shown below:

If you have any other questions please feel free to contact me.

The pbix file is attached.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[naveenwills]

Thanks for the reply @v-huijiey-msft 

Apologies for the late reply as I was away sick. I have already done this in my current solution. The problem I have with this is other columns of information I want to use gets grouped / stripped out.  

Those columns are really important for relationships with other tables and thats why was wondering if there is other way to achieve this solution. 

One of the other visual I want to use with all this also looks like this

Thanks

[v-huijiey-msft]

Hi @naveenwills ,

Please try:

Create a calculated column in your master table that flags the first entry of each day.

First Entry Flag = 
VAR CurrentDate = Master[Date]
VAR CurrentLocation = Master[Location]
RETURN IF (
    MINX (
        FILTER (
            Master,
            Master[Date] = CurrentDate && Master[Location] = CurrentLocation
        ),
        Master[Entry Time]
    ) = Master[Entry Time],
    1,
    0
)

Create a measure to count the number of unique days in a week that have an entry.

Weekly Occurrences = 
CALCULATE (
    DISTINCTCOUNT (Master[Date]),
    Master[First Entry Flag] = 1
)

I would appreciate it if you could provide me with sample data for testing, not in the form of screenshots, please remove any sensitive data in advance.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!