cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper I

## 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)

Anonymous
Not applicable

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

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors