topic Re: Conditional Statement to Exclude Balance of Current Month From Results in DAX Commands and Tips

Conditional Statement to Exclude Balance of Current Month From Results

CracktheCode85 — Tue, 04 Jun 2024 19:24:05 GMT

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. 🙂

Re: Conditional Statement to Exclude Balance of Current Month From Results

Moetazzahran — Tue, 04 Jun 2024 21:18:50 GMT

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]),

)

If the solutions answers your problem, pls mark my post as a solution and your Kudos is much appreciated!

Re: Conditional Statement to Exclude Balance of Current Month From Results

CracktheCode85 — Wed, 05 Jun 2024 13:49:26 GMT

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?

Re: Conditional Statement to Exclude Balance of Current Month From Results

Moetazzahran — Wed, 05 Jun 2024 14:06:26 GMT

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

Re: Conditional Statement to Exclude Balance of Current Month From Results

CracktheCode85 — Wed, 05 Jun 2024 14:15:49 GMT

Re: Conditional Statement to Exclude Balance of Current Month From Results

Moetazzahran — Wed, 05 Jun 2024 14:18:53 GMT

This is your base date table, however you can use the feature I showed in my previous reply when you drag and drop within your visuals.

I assume you are writing a measure to be used in a visual. Correct?

Thank you

Re: Conditional Statement to Exclude Balance of Current Month From Results

CracktheCode85 — Wed, 05 Jun 2024 14:24:56 GMT

Correct- I am trying to write this measure correctly to become the Denominator of another measure. The logic used for this calculation is based on two date columns.... which is challenging in it's own way 🙂

This formula will be divided by the CurrentMonth Total sales.
So I'm trying to get the TotalSales of the CurrentMonth divided by the Total Sales of any Sale due outside of the CurrentMonth. (Hope I'm explaining that well enough....I'm still grappling with it myself in the BI World since in the SQL world it makes a bit more sense.)

Re: Conditional Statement to Exclude Balance of Current Month From Results

Anonymous — Mon, 10 Jun 2024 06:20:21 GMT

Hi CracktheCode85

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.

Re: Conditional Statement to Exclude Balance of Current Month From Results

CracktheCode85 — Tue, 11 Jun 2024 18:23:10 GMT

This 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?