Forum Discussion
Helper IIConditional Statement to Exclude Balance of Current Month From Results
Hi Brilliant Folks!
I'm struggling with writing a DAX formula that will do the same as this conditional statement:
Due Current = IF(Due_Date > Current Month, Then Total_Sales, Else '0', EndIF)
Essentially I need the Total_Sales for any Sale that has a Due_Date that is NOT in the current month.
My Due_Date imported as a Hierarchy (not sure if that matters).
I'm scrubbing through the function library and it's becoming overwhelming to find the right answer.
Thank you for your insight and consideration. 🙂
Based on your needs, I have created the following table.
You can use the following DAX to get the Total_Sales for any Sale that has a Due_Date that is NOT in the current month.Total_Sales = VAR current_month = MONTH(TODAY()) RETURN CALCULATE(SUM('Table'[Sales]),FILTER('Table',MONTH('Table'[Due_Date])<current_month))Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Based on your needs, I have created the following table.
You can use the following DAX to get the Total_Sales for any Sale that has a Due_Date that is NOT in the current month.Total_Sales = VAR current_month = MONTH(TODAY()) RETURN CALCULATE(SUM('Table'[Sales]),FILTER('Table',MONTH('Table'[Due_Date])<current_month))Result:
Best Regards,
Jayleny
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
CracktheCode85This one seemed to work perfectly for one month. How can I make this 'Current Month' field variable so that if I use a slicer to look at a different month it maintains the currentmonth look back rule?
Example:
Currently it filters out June, which is perfect for results in May.
However if I look back at April it still filters out June only. How can I write this to make it filter out May when I'm looking at April and then have that same function work for May?
MoetazzahranResolver II
Hello,
I have worked on the problem you have provided using assumptions, please let me know if it works.Sale_ID
Due_Date
Total_Sales
1
2024-05-20
100
2
2024-06-05
200
3
2024-07-10
300
4
2024-06-15
400
5
2024-08-01
500
Assuming the current date is 2024-06-03
Sale_ID
Due_Date
Total_Sales
Due_Current
1
2024-05-20
100
100
2
2024-06-05
200
0
3
2024-07-10
300
300
4
2024-06-15
400
0
5
2024-08-01
500
500
Due_Current =
VAR CurrentMonth = MONTH(TODAY())
VAR CurrentYear = YEAR(TODAY())
RETURN
SWITCH(
TRUE(),
MONTH(EstimateSummaryDataDetails[Due_Date]) <> CurrentMonth || YEAR(EstimateSummaryDataDetails[Due_Date]) <> CurrentYear, SUM(CostUnpivot[Total_Sales]),
0
)
If the solutions answers your problem, pls mark my post as a solution and your Kudos is much appreciated!- CracktheCode85
Thank you for providing this!
When I get to the
MONTH(EstimateSummaryDataDetails[Due_Date]) I can't input any due date values as the Month function calls for a predefined measure or a caculated one. My due dates are imported as a hierarchy and do not show up as an option for this portion of the formula.
How can I get the Due Date out of the hierarchy format and input it into this formula?- Moetazzahran
Can you please send me a screenshot of your due date format?
If you don't want the date hierarchy format in your visuals, you can change it this way