IF function help

[Anonymous]

Hi!

I need help solving why my IF function is not working properly.  Power BI accepts the dax formula as correct, but it is not populating when I put it into a visual (Card or Table).

What I am looking to solve for is:

If a customer does not have any sales in the last 4 months, then the customer is considered "Inactive".

And the latter - If the customer does have sales in the last 4 months, then the customer would be considered "Active".

My previous 4 months dax formula looks like:

Previous 4 Months =

CALCULATE(SUM('VALUE ENTRY'[Sales Amount (Actual)]),DATESINPERIOD('Calendar'[Date],MAX('Calendar'[Date]),-4,MONTH))

My IF function looks like:

Many thanks in advance! 

[Anonymous]

Hi @Anonymous ,

I created a sample pbix file(see the attachment), please check if that is what you want. You can create a measure as below to get it:

Inactive = 
VAR _enddate =
    TODAY ()
VAR _sales =
    CALCULATE (
        SUM ( 'VALUE ENTRY'[Sales Amount (Actual)] ),
        FILTER (
            'VALUE ENTRY',
            'VALUE ENTRY'[Sales Date]
                > EOMONTH ( _enddate, -4 ) + 1
                && 'VALUE ENTRY'[Sales Date] <= _enddate
        )
    )
RETURN
    IF ( _sales > 0, "Active", "Inactive" )

In addition,  you can refer the following thread to get it.

Active Customers if has orders in last X months

If the above one can't help you get the desired result, please provide some sample data in your table 'VALUE ENTRY'  (exclude sensitive data) with Text format and your expected result with backend logic and special examples. By the way, the last 4 months means from the selected date to current date(today) or something else?  It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

Hi @Anonymous,

Sure thing! 

I'm looking to build this formula off of a data filter - ex below: 9/1/2021 thru 9/30/2022

Here is a sample of my data:

I was trying to build an IF statement off of my previous 4 months measure:

IF measure:

But as you can see above, in September, it shows the customer as being 'active', instead of 'inactive'.

[Anonymous]

Hi @Anonymous ,

Could you please provide some raw data instead of the data after calculated on the visual? There is no customer info in the table... Base on your provided data, what's the correct result? It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. And how do you judge the custom is active or inactive? Could you please share the related logic. Whether the sales on selected month is greater than 0, then active? Otherwise, inactive? Because according to the logic which you shared before, the displayed result is correct since the the sales for Previous 4 Months are all greater than 0.... Thank you.

How to upload PBI in Community

Best Regards

[Anonymous]

@Greg_Deckler  Thanks for the response!

hmm, I updated the formula format to the same as your suggestion, but unfortunately it's still not working 😞 

[Greg_Deckler]

@Anonymous Try something like this:

Previous 4 Months =
  VAR __MaxDate = MAX('Calendar'[Date])
  VAR __MonthsAgo = EOMONTH(__MaxDate,-4)
  VAR __MinDate = DATE(YEAR(__MonthsAgo),MONTH(__MonthsAgo),DAY(__MaxDate))
  VAR __Table = FILTER('VALUE ENTRY', [Date] >= __MinDate && [Date] <= __MaxDate)
  VAR __Result = SUMX(__Table, [Sales Amount (Actual)]
RETURN
  __Result

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TITHW/m-p/434008

[Greg_Deckler]

@Anonymous Try:

Inactive = IF([Previous 4 Months] < 0, "Inactive", "Active")