Need help on dax measure for the count on the date of maximum total daily count within each month

[LanrexTee]

Hi Team 

Could you pls help me on either of below two tasks? Even if I get Task1 resolved, it's fine. I dont want to use a calculated table.

dataTable

product	Shoptype	Shopname	date_val	monthnumber
Bicycle	shopA	a1	2024-04-09	4
Bicycle	shopA	a2	2024-04-09	4
Bicycle	shopA	a3	2024-04-11	4
Bicycle	shopA	a4	2024-04-11	4
Bicycle	shopA	a5	2024-04-10	4
Bicycle	shopA	a6	2024-04-10	4
Bicycle	shopA	a7	2024-04-10	4
Bicycle	shopA	a8	2024-05-01	5
Bicycle	ShopB	b1	2024-04-10	4
Bicycle	ShopB	b2	2024-04-09	4
Bicycle	ShopB	b3	2024-04-09	4
Bicycle	shopB	b4	2024-05-09	5
Bicycle	shopB	b5	2024-05-09	5
Motorcycle	shopA	a1	2024-04-09	4
Motorcycle	shopA	a2	2024-04-09	4
Motorcycle	shopA	a3	2024-04-09	4
Motorcycle	shopA	a4	2024-04-11	4
Motorcycle	shopA	a5	2024-04-11	4
Motorcycle	shopA	a6	2024-04-11	4
Motorcycle	shopA	a7	2024-04-11	4
Motorcycle	ShopA	a8	2024-05-09	5
Motorcycle	ShopB	b1	2024-04-09	4
Motorcycle	ShopB	b2	2024-04-11	4
Motorcycle	ShopB	b3	2024-04-09	4
Motorcycle	ShopB	b4	2024-05-01	5

Pivot Table for the dataset above

1- Task1: I need a measure to get the count of the shopname on the date that the total daily count (of ShopA+ShopB )  is maximum for each month. 

Desired Result:

2- Task2: In case there're multiple days within a month in which total daily maximum count of shopnames for each product is maximum, select the date of the count to display to be on the day which ShopA has highestcount. 

Desired Result would be:

Thanks

[LanrexTee]

Many thanks @v-jianpeng-msft 

Possibly the sample data I provided wasn't sufficient as I tried the above but didn't work. I'm attaching the closest sample pbix with the data similar to the actual data.

I tried a new measure shown in the snip below, the daily maximum count I got for each product  in each month or quarter  is correct. However, if I sum up the the count displayed under each shoptype for each product in the monthly visual (the lower table), it isnt matching the actual total daily count (upper table), it is instead giving me the maximum in a month for each shoptype.

Note that I need a distinct count of the shopname each day because there'e duplicates and I dont want the shopname to be counted twice for each product in the same day.

All I need is to get the distinctcount of  (shopname) for each product  under each shoptype on the day that the total distinct count(shopname) is maximum in each month. I'll like that to be correct for the Quarter or year as well.

Pls see the link to my pbix, kindly help me review the correct fix for this:

https://drive.google.com/file/d/14TCLlLFUEIT9ebSACxmmAKkiedUzlR8Z/view?usp=sharing

I'm using

Count of shopname max per date_val =

MAXX(

    KEEPFILTERS(VALUES('Table'[date_val])),

    CALCULATE(DISTINCTCOUNT('Table'[shopname]))

)

Thanks

Lanrex

[v-jianpeng-msft]

Hi, @LanrexTee 

Thank you very much for your reply. Do you need a distinct count of shopnames for the day when the sum of shoptype columns, ShopA+ShopB, is the largest sum of shoptypes, or do you need distinct counts of shopnames for each day of the month?

Best Regards

Jianpeng Li

[LanrexTee]

Thank you @v-jianpeng-msft , I need it for the single day in each month (and subsequently each Quarter, year depending on the drill down of the date column)  where the count of shopA+ShopB is maximum.

[v-jianpeng-msft · Accepted solution]

Hi, @LanrexTee 

Thank you very much for your reply. After my re-editing of my DAX expression, here's a possible DAX:

Task1 =
VAR _seletedmonth =
    SELECTEDVALUE ( 'Table'[date_val].[Month] )
VAR _seletedquater =
    SELECTEDVALUE ( 'Table'[date_val].[Quarter] )
VAR _seletedyear =
    SELECTEDVALUE ( 'Table'[date_val].[Year] )
VAR _seletedday =
    SELECTEDVALUE ( 'Table'[date_val].[Day] )
VAR _table =
    SUMMARIZE (
        'Table',
        'Table'[date_val],
        'Table'[date_val].[Year],
        'Table'[date_val].[Month],
        'Table'[date_val].[Quarter],
        'Table'[date_val].[Date],
        'Table'[shoptype],
        'Table'[product],
        "Count1",
            VAR _shopA =
                CALCULATE (
                    DISTINCTCOUNT ( 'Table'[shopname] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[product] ),
                        'Table'[date_val] = EARLIER ( 'Table'[date_val] )
                            && 'Table'[shoptype] = "shopA"
                    )
                )
            VAR _shopB =
                CALCULATE (
                    DISTINCTCOUNT ( 'Table'[shopname] ),
                    FILTER (
                        ALLEXCEPT ( 'Table', 'Table'[product] ),
                        'Table'[date_val] = EARLIER ( 'Table'[date_val] )
                            && 'Table'[shoptype] = "shopB"
                    )
                )
            RETURN
                CALCULATE ( _shopA + _shopB )
    )
VAR _maxcount =
    MAXX (
        FILTER (
            _table,
            'Table'[date_val].[Month] = _seletedmonth
                || 'Table'[date_val].[Year] = _seletedyear
                || 'Table'[date_val].[Quarter] = _seletedquater
                || 'Table'[date_val].[Date] = _seletedday
        ),
        [Count1]
    )
VAR _correspondday =
    CALCULATE ( MAX ( 'Table'[date_val] ), FILTER ( _table, [Count1] = _maxcount ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[shopname] ),
        FILTER ( 'Table', 'Table'[date_val] = _correspondday )
    )

The results are shown in the figure below:

I've uploaded the PBIX file I used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-jianpeng-msft]

Hi, @LanrexTee 

Based on your description and the data provided, I used the following sample data:

I use the following DAX expression to create a calculated column to extract the month of the date:

Month = MONTH('Table'[date_val])

First, for task1, I created a measure using the following DAX expression:

Task1 =
VAR _shopA =
    COUNTAX ( FILTER ( 'Table', 'Table'[Shoptype] = "shopA" ), 'Table'[Shoptype] )
VAR _shopB =
    COUNTAX ( FILTER ( 'Table', 'Table'[Shoptype] = "shopB" ), 'Table'[Shoptype] )
VAR _seletedmonth =
    SELECTEDVALUE ( 'Table'[Month] )
VAR _table =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[date_val],
        "Count1",
            VAR _A =
                CALCULATE (
                    COUNTA ( 'Table'[Shoptype] ),
                    FILTER ( ALLEXCEPT ( 'Table', 'Table'[date_val] ), 'Table'[Shoptype] = "shopA" )
                )
            VAR _B =
                CALCULATE (
                    COUNTA ( 'Table'[Shoptype] ),
                    FILTER ( ALLEXCEPT ( 'Table', 'Table'[date_val] ), 'Table'[Shoptype] = "shopB" )
                )
            RETURN
                _A + _B,
        "Month", MONTH ( 'Table'[date_val] )
    )
VAR _maxcount =
    MAXX ( FILTER ( _table, [Month] = _seletedmonth ), [Count1] )
VAR _correspondday =
    CALCULATE ( MAX ( 'Table'[date_val] ), FILTER ( _table, [Count1] = _maxcount ) )
RETURN
    CALCULATE (
        COUNTA ( 'Table'[Shoptype] ),
        FILTER ( 'Table', 'Table'[date_val] = _correspondday )
    )

Put this measure into the matrix visual as follows:

For task2, I created a measure using the following DAX expression:

Task2 =
VAR _seletedmonth =
    SELECTEDVALUE ( 'Table'[Month] )
VAR _product =
    SELECTEDVALUE ( 'Table'[product] )
VAR _table =
    SUMMARIZE (
        ALL ( 'Table' ),
        'Table'[date_val],
        'Table'[Shoptype],
        'Table'[Month],
        'Table'[product],
        "Count2", CALCULATE ( COUNTA ( 'Table'[Shoptype] ) )
    )
VAR _maxcount =
    MAXX (
        FILTER ( _table, 'Table'[product] = _product && 'Table'[Month] = _seletedmonth ),
        [Count2]
    )
VAR _correspondday =
    CALCULATE ( MAX ( 'Table'[date_val] ), FILTER ( _table, [Count2] = _maxcount ) )
RETURN
    CALCULATE (
        COUNTA ( 'Table'[Shoptype] ),
        FILTER ( 'Table', 'Table'[date_val] = _correspondday )
    )

Put this measure into the matrix visual as follows:

I've provided the PBIX file used this time below.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.