Need DAX Command to return Date of Max Sales by Day of Week

[DannyM]

Hi Folks - I need a guidance finishing up this DAX command that I just cant seem to get my head around.

I have a table that holds Data Source, Date, Day of Week, and Sales Amount. I need to return a visualization with the highest days sales by source and day of week but I cant seem to get the DAX right for the correct sales date.

Right now here is what I have but it does not provide the max date within day of week, just max date overall. How can I change this to get the day of week filter to work in it? Thanks.

Highest Sale Date = CALCULATE(MAX('AggSumSales'[DeliveryDate]),FILTER('AggSumSales', 'AggSumSales'[Sales]=MAX(AggSumSales[Sales])))

[v-dineshya]

Hi @DannyM ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @DannyM ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @DannyM ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[v-dineshya]

Hi @DannyM ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[DataNinja777 · Accepted solution]

Hi @DannyM ,

To return the correct date of maximum sales by DataSource and Day of Week (DOW), you need to adjust your DAX formula to calculate the maximum date specifically within the combination of DataSource and DOW. Your current formula is returning the max date overall, without filtering properly by DOW. To fix this, use the following DAX formula:

Highest Sale Date = 
CALCULATE (
    MAX('AggSumSales'[DeliveryDate]),
    FILTER (
        ALL('AggSumSales'),
        'AggSumSales'[DataSource] = MAX('AggSumSales'[DataSource]) &&
        'AggSumSales'[DOW] = MAX('AggSumSales'[DOW]) &&
        'AggSumSales'[Sales] = 
            CALCULATE (
                MAX('AggSumSales'[Sales]),
                FILTER (
                    ALL('AggSumSales'),
                    'AggSumSales'[DataSource] = MAX('AggSumSales'[DataSource]) &&
                    'AggSumSales'[DOW] = MAX('AggSumSales'[DOW])
                )
            )
    )
)

This measure works by first calculating the maximum sales for a specific combination of DataSource and DOW, and then returning the corresponding delivery date for that value. The ALL function removes any external filters so that the inner and outer filters can apply the correct logic. To return the highest sales amount itself, use this separate measure:

Highest Sales Amount = 
CALCULATE (
    MAX('AggSumSales'[Sales]),
    FILTER (
        ALL('AggSumSales'),
        'AggSumSales'[DataSource] = MAX('AggSumSales'[DataSource]) &&
        'AggSumSales'[DOW] = MAX('AggSumSales'[DOW])
    )
)

These two measures together will allow your visual to show the maximum sales amount and the corresponding date for each day of the week per data source.

Best regards,