topic Re: Calculate sum over all selected years in DAX Commands and Tips

Calculate sum over all selected years

MEkin — Mon, 06 Feb 2023 09:57:03 GMT

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

)

Re: Calculate sum over all selected years

FreemanZ — Mon, 06 Feb 2023 11:19:46 GMT

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

)

Re: Calculate sum over all selected years

MEkin — Mon, 06 Feb 2023 11:33:38 GMT

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.

Re: Calculate sum over all selected years

FreemanZ — Mon, 06 Feb 2023 12:15:27 GMT

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

)

Re: Calculate sum over all selected years

MEkin — Mon, 06 Feb 2023 12:54:47 GMT

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

)

Re: Calculate sum over all selected years

Anonymous — Thu, 09 Feb 2023 06:10:02 GMT

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.

Re: Calculate sum over all selected years

MEkin — Thu, 09 Feb 2023 05:52:56 GMT

Hello Anonymous , 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

Re: Calculate sum over all selected years

Anonymous — Thu, 09 Feb 2023 01:30:36 GMT

Hi MEkin ,

Please refer to:

How to provide sample data in the Power BI Forum

Best Regards,

Neeko Tang

Re: Calculate sum over all selected years

MEkin — Thu, 09 Feb 2023 05:54:08 GMT

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

Re: Calculate sum over all selected years

Anonymous — Thu, 09 Feb 2023 06:00:50 GMT

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

Re: Calculate sum over all selected years

MEkin — Thu, 09 Feb 2023 06:05:56 GMT

I'm sorry, and this one.

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

Re: Calculate sum over all selected years

MEkin — Fri, 10 Feb 2023 14:20:57 GMT

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