Month of Last Sales Date

[JimSales81]

I'm trying to write a code showing the Month/Period of the Most recent Sales Date for anyone in the org.

Here is my max date formula

Max Date = calculate(Max('FACT Detail' [Invoice Date]), all('FACT Detail')

This seems to work to display the max date for regardless of if the sales person had sales that day.

Here is my formula for Month of max date.  When a salesperson doesn't have a sale for the most recent sales date the month goes blank and his MTD Sales number disappear.  I want it to say Jan 202401

Month of Max Date = Calculate(max('DIM Calendar'[Year-Period (Fiscal)]),filter('FACT Detail','FACT Detail'[Invoice Date]=[Max Date]))

I assume i have to throw an All on the FACT Detail table but where in the formula should i insert it?

THanks,
Jim

[123abc · Accepted solution]

It seems like you're working with DAX formulas in Power BI or a similar tool to calculate the month of the most recent sales date. You're correct in assuming that you need to handle cases where a salesperson doesn't have a sale for the most recent sales date.

To address this issue, you should ensure that your calculation doesn't filter out the salesperson if they don't have a sale on the most recent date. You can achieve this by modifying your DAX formula as follows:

Month of Max Date =
CALCULATE (
MAX ( 'DIM Calendar'[Year-Period (Fiscal)] ),
FILTER (
ALL ( 'FACT Detail' ),
'FACT Detail'[Invoice Date] = [Max Date]
)
)

Here's the breakdown of what's changed:

1. ALL('FACT Detail'): This function removes any filters applied to the 'FACT Detail' table, ensuring that all sales are considered when filtering for the most recent sales date.

2. FILTER(): This function applies a filter to the 'FACT Detail' table, selecting only the rows where the 'Invoice Date' matches the maximum date ([Max Date]).

By using the ALL function, you're ensuring that even if a salesperson doesn't have a sale on the most recent date, they are still considered when determining the month of the maximum date. This should prevent the month from going blank and keep your MTD (Month-to-Date) sales numbers intact.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.