help with getting the correct "else" value in my RETURN

[Anonymous]

Hello Community  -  My goal, which should be really easy, is to get the March 22 column to say Pending, and the April 22 column below to say  - .     Essentially, if last month's net revenue is blank (because we have not reconciled it yet), then I want the cell to say "Pending".    If it is not last month (essentially all other months from this month forward), then it should just be a dash (-) 

Part of the formula is working, but I can't get the false (else) statement to work...and instead of showing a dash, it just shows the word "Pending".    So in my example below, April 22 should just show  " - "

Net Revenue =

VAR _Reconciled = FILTER('Net Revenue Table','Net Revenue Table'[Reconciled?] = "Yes")

VAR _current_month = MONTH(TODAY())

VAR _Previousmonth = CALCULATE(SUM('Net Revenue Table'[Net Revenue]),FILTER('Net Revenue Table',MONTH('Net Revenue Table'[Period Ending]) = _current_month -1))

RETURN

IF(SELECTEDVALUE('Net Revenue Table'[Reconciled?]) = "Yes",[Sum of Net Revenue],IF(_Previousmonth = BLANK(),"Pending","-"))

[Anonymous]

Hi @Anonymous,

The current DAX expression does not support to output multiple data types, if you want to use numeric values and text at the same time, please add the operation to convert the output values to text at first.

BTW, if you convert these values to text, they will face issues working with aggregation features. (the text type does not work for common math aggregations such as sum, max, average)

Regards,

Xiaoxin Sheng

[m3tr01d]

hello @Anonymous ,

First, I have a question :
Why do you have a variable _Reconciled  if you are not using it in the measure?

Second, your issue is the variable _PreviousMonth

VAR _Previousmonth =
CALCULATE (
    SUM ( 'Net Revenue Table'[Net Revenue] ),
    FILTER (
        'Net Revenue Table',
        MONTH ( 'Net Revenue Table'[Period Ending] ) = _current_month - 1
    )
)

When the measure is evaluate inside Apr 22 column, the filter context will contains Apr 22. 
It means that we your measure is trying to evaluate the filter statement :

FILTER (
        'Net Revenue Table',
        MONTH ( 'Net Revenue Table'[Period Ending] ) = _current_month - 1
    )

The table 'Net Revenue Table' is already filtered for Apr 22. It will try to filter this table and find rows where Month( 'Net Revenue Ending'[Period Ending] ) = Mar. It will always return Blank.

You need to ignore the filters applied on 'Net Revenue Ending'[Period Ending] then look for Mar data. Try to replace your valriable with this :

VAR _Previousmonth =
CALCULATE (
    SUM ( 'Net Revenue Table'[Net Revenue] ),
    FILTER (
        ALL( 'Net Revenue Table'[Period Ending] ),
        MONTH ( 'Net Revenue Table'[Period Ending] ) = _current_month - 1
    )
)

I don't know it will work because I don't know exactly what are other filters applied on this table.