cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors