cancel
Showing results for
Did you mean:
Frequent Visitor

## Sum first date of the year & ignore slicer

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.

1 ACCEPTED SOLUTION
Community Support

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

3 REPLIES 3
Community Support

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

Super User

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

Super User

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)

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

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors