Sum first date of the year & ignore slicer

[r_fioretti]

Hello,

I’m trying to get the sum of just the first day of the year of the applied slicer. But I can’t figure out how to use just the year of the slicer to get it done.

In the CALCULATE() when I use just the ALLEXCEPT() I get the sum of all values and when I use the ALLEXCEPT() with FILTER() I get a blank result.

I’ve searched for similar issues, but couldn’t find any answer that solve this problem.

[v-easonf-msft · Accepted solution]

Hi, @r_fioretti 

You may need to encapsulate all conditions into function 'filter'.

Please check if the following formula works for you.

Saldo Inicial(YTD) =
VAR bg_dt =
    FIRSTDATE ( DATESYTD ( 'Caixa(YTD)'[Data] ) )
VAR si =
    CALCULATE (
        SUM ( 'Caixa(YTD)'[Valor] ),
        FILTER (
            ALLEXCEPT ( 'Caixa(YTD)', 'Caixa(YTD)'[Data] ),
            'Caixa(YTD)'[Valor] = "Saldo Inicial"
                && 'Caixa(YTD)'[Data] = bg_dt
        )
    )
RETURN
    si

Best Regards,
Community Support Team _ Eason

[tamerj1]

Hi @r_fioretti 

please try

VAR hg_dt = DATE(YEAR(MIN('Caixa (YTD) '[Datal])), 1, 1)

[Anonymous]

Hi

Firts get the year selected ==> var selectedyear=selectevalue(date[year])

calculate the first day of the year= var firstday=calcualte(min(date[Date]),date[year]=selectedyear)

and after your sum 

calculate(sum(x), date[date]=firstday)