Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
JimSales81
Frequent Visitor

Month of Last Sales Date

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
1 ACCEPTED SOLUTION
123abc
Community Champion
Community Champion

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.

View solution in original post

1 REPLY 1
123abc
Community Champion
Community Champion

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors