Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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:
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.
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.
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:
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.
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.
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
85 | |
54 | |
45 | |
39 | |
21 |