Calculate sum over all selected years

[MEkin]

Hello, I don't know if I must introduce myself, but I have a question about a DAX-formula.

I want a line in a visual that shows the total sum over the selected years, I have now a formula that begins with the start of a year till the end of that year from the selected filter from the slicer.

But I want that it starts from the minimum year to the maximum year of the selected years from the slicer.

Netto Opgeslagen Energie =

 CALCULATE (

    SUM (view_Energie[Energie_Totaal_MWh]),

    FILTER(ALL(view_Energie) ,

    view_Energie[Datum].[Date] <= MAX( view_Energie[Datum].[Date] )),

    view_Energie[Datum].[year] = SELECTEDVALUE(view_Energie[Datum].[Year])

    )

[MEkin · Accepted solution]

Hello, I found the right syntax, it works now!

Netto Opgeslagen Energie = SUMX(FILTER(ALLSELECTED(view_Energie),

view_Energie[Datum].[Date] = MAXX(view_Energie,view_Energie[Datum].Date])),view_Energie[Energie_Totaal_MWh])

[v-tangjie-msft]

Hi @MEkin ,

Please try this:

Netto Opgeslagen Energie 2 =
var _min=CALCULATE(MIN('Table'[date]),VALUES('Table'[date]))
var _max=CALCULATE(MAX('Table'[date]),VALUES('Table'[date]))
return
CALCULATE (
SUM (view_Energie[Energie_Totaal_MWh] ),
filter(ALL(view_Energie),
view_Energie[Datum]>= _min
&& view_Energie[Datum] <= _max
)
)

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[MEkin]

Hello @v-tangjie-msft , thanks for your reply.

It didn't work, it selects the min and max value, but doesn't apply to the query.

Here's the document, It would be great if we can fix this.

https://we.tl/t-f8R22YXzFa

[v-tangjie-msft]

Hi @MEkin ,

Please refer to:

How to provide sample data in the Power BI Forum

Best Regards,

Neeko Tang

[MEkin]

Hi, I uploaded it with WeTransfer, can you take a look? Thanks!

[v-tangjie-msft]

Hi @MEkin ,

Unfortunately, I can't open your link. Please check if your link is private, is your PBIX file private?

Best Regards,

Neeko Tang

[MEkin]

I'm sorry, and this one.

https://drive.google.com/file/d/1Hbq0sjcwNAcYR0mGujcWtfXECAZqjKRf/view?usp=sharing

[MEkin]

Hi, thanks again, but also starts with each year over.

I have a working formula, but this starts from the beginning of the table, not from the minimum value of the selected slicer. If we can adjust this one to start from the MIN(Slicer) it will be the solution. 

In the picture u see -207, but this is a sum from the year 2013 from the table.

FilterStartDate = MIN( view_Energie[Datum].[Date] )

StartDate = MIN( view_Energie[Datum].[Date] )

Netto Opgeslagen Energie 2 =

CALCULATE (

SUM (view_Energie[Energie_Totaal_MWh] ),

filter(ALL(view_Energie),

view_Energie[Datum].[Date] >= MIN([FilterStartDate],[StartDate] )

&& view_Energie[Datum].[Date] <= MAX(view_Energie[Datum].[Date] )

)

)

[MEkin]

Hello FreemanZ, thanks for your reply, unfortunally I already had this calculation, this one also starts from january in the next year.

See the screenshot, I would like to continue the line over the year, independed of the years. The put a red line how it should go over to next year and not start again counting from january.

[FreemanZ]

hi @MEkin 

then how about:

Netto Opgeslagen Energie =

CALCULATE (

    SUM (view_Energie[Energie_Totaal_MWh]),

    FILTER(

       ALL(view_Energie) ,

       view_Energie[Datum] <= MAX( view_Energie[Datum] ))

    )

)

[FreemanZ]

hi @MEkin 

try like:

Netto Opgeslagen Energie =

CALCULATE (

    SUM (view_Energie[Energie_Totaal_MWh]),

    FILTER(

       ALL(view_Energie) ,

       view_Energie[Datum] <= MAX( view_Energie[Datum] )),

     YEAR(view_Energie[Datum])= YEAR(SELECTEDVALUE(view_Energie[Datum])

    )

)