Forum Discussion

SebaSpotti's avatar
SebaSpotti
Icon for Advocate II rankAdvocate II
1 year ago
Solved

Empty selectvalue eve if is not empty

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! 

2 Replies