Empty selectvalue eve if is not empty

[SebaSpotti]

Hi guys!

I have this calculate table: 

Last12Month = 
VAR ___Date = SELECTEDVALUE(MonthDate[Month])
VAR ___StartDate = DATE(YEAR(___Date),MONTH(___Date),1)
//VAR ___StartDate = TODAY()
VAR DateTable =
    ADDCOLUMNS(
        CALENDAR(___StartDate-365,___StartDate),
        "MonthEnd", EOMONTH([Date],0),
        "MonthStart", DATE(YEAR([Date]),MONTH([Date]),1)
    )
VAR ResultTable =
SUMMARIZE(
    DateTable,
    [MonthEnd],
    "NTR Rolling",
    VAR auxDate = [MonthEnd]
    VAR CurrentDate = EOMONTH(auxDate, 0)
    VAR FilteredTable = 
        CALCULATE(
            SUM(SS_NTR_BY_DAY[Conteggio CMs]),
            FILTER(
                SS_NTR_BY_DAY,
                SS_NTR_BY_DAY[STATUS_START_DATE] > (CurrentDate - 365) &&
                SS_NTR_BY_DAY[STATUS_START_DATE] <= CurrentDate
            )
        )
    RETURN FilteredTable,
    "HC Rolling AVG",
    VAR auxDateHC = [MonthEnd]
    VAR CurrentDateHC = DATE(YEAR(auxDateHC), MONTH(auxDateHC), 1)
    VAR HC = 
        CALCULATE(
            SUM(SS_HC_BY_DAY[Conteggio CMS]) / 365,
            FILTER(
                SS_HC_BY_DAY,
                SS_HC_BY_DAY[COMP_DATE] > (CurrentDateHC - 365) &&
                SS_HC_BY_DAY[COMP_DATE] <= CurrentDateHC
            )
        )
    RETURN HC,
    "Rolling Turnover",
    VAR auxDate = [MonthEnd]
    VAR CurrentDate = EOMONTH(auxDate, 0)
    VAR NTRRolling = 
        CALCULATE(
            SUM(SS_NTR_BY_DAY[Conteggio CMs]),
            FILTER(
                SS_NTR_BY_DAY,
                SS_NTR_BY_DAY[STATUS_START_DATE] > (CurrentDate - 365) &&
                SS_NTR_BY_DAY[STATUS_START_DATE] <= CurrentDate
            )
        )
    VAR auxDateHC = [MonthEnd]
    VAR CurrentDateHC = DATE(YEAR(auxDateHC), MONTH(auxDateHC), 1)    
    VAR HCRollingAVG = 
        CALCULATE(
            SUM(SS_HC_BY_DAY[Conteggio CMS]) / 365,
            FILTER(
                SS_HC_BY_DAY,
                SS_HC_BY_DAY[COMP_DATE] > (CurrentDateHC - 365) &&
                SS_HC_BY_DAY[COMP_DATE] <= CurrentDateHC
            )
        )
    RETURN DIVIDE(NTRRolling, HCRollingAVG)

)

RETURN ResultTable

I got an error, that the SELECTEDVALUE is empty. But if I isolate that measure, is not so.

Any suggestion how to solve this problem?

Thanks all! 

[Bibiano_Geraldo]

Hi @SebaSpotti ,

The issue with your code arises because SELECTEDVALUE(MonthDate[Month]) is returning blank in the context where the calculated table is being evaluated. This happens because a calculated table is evaluated in a global context, not in the context of a specific row or filter.

Take off SELECTEDVALUE function and keep only something like this:

VAR ___Date = MonthDate[Month]

If this help you, please consider to accept this reply as solution and give a Kudo.

thannk you

[dk_dk · Accepted solution]

Hi @SebaSpotti 

Calculated tables are evaluated with data refresh, so SELECTEDVALUE will not work this way. (I assume your intention was that when you change the slicer selection for Month, the calculated table recalculates with that month, which is not possible)

See these previous threads for proposed workarounds:

https://community.fabric.microsoft.com/t5/Desktop/Calculated-Table-using-SELECTEDVALUE/m-p/911501
https://community.fabric.microsoft.com/t5/Desktop/Using-SELECTEDVALUE-with-Calculated-Table-workarou...