topic Calculate a hourly difference per dimensions in DAX Commands and Tips

Calculate a hourly difference per dimensions

Stella_V — Mon, 23 Oct 2023 15:20:38 GMT

Hello

I am trying to calculate a hourly difference, for the selected day in a date slicer, between two attributes.

More precisely I want to calculate th difference in "Value" between France and Austria, per hour on the same day, for the attribute 'forecast1'.

	Time	Hour	Attribute1	Attribute2	Value
D	24.10.2023	1.00	Austria	Forecast1	10
D	24.10.2023	2.00	Austria	Forecast1	20
D	24.10.2023	3.00	Austria	Forecast1	30
D	24.10.2023	4.00	Austria	Forecast1	40
D	24.10.2023	5.00	Austria	Forecast1	50
D	24.10.2023	1.00	France	Forecast1	25
D	24.10.2023	2.00	France	Forecast1	140
D	24.10.2023	3.00	France	Forecast1	60
D	24.10.2023	4.00	France	Forecast1	50
D	24.10.2023	5.00	France	Forecast1	65

	Result	hour	Attribute3	Attribute2	Value
	24.10.2023	1.00	France-Autria	Forecast1	15
	24.10.2023	2.00	France-Autria	Forecast1	120
	24.10.2023	3.00	France-Autria	Forecast1	30
	24.10.2023	4.00	France-Autria	Forecast1	10
	24.10.2023	5.00	France-Autria	Forecast1	15

Can you please help me? I did a direct query.

thank you very much !

Stella

Re: Calculate a hourly difference per dimensions

barritown — Mon, 23 Oct 2023 20:12:54 GMT

Hi Stella_V,

If you want to solve your task via a Table visual, you could create such a measure and use it:

In plain text:

NewValue = SUMX ( FILTER ( Data, [Attribute1] = "France" ), [Value] ) - SUMX ( FILTER ( Data, [Attribute1] = "Austria" ), [Value] )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

Re: Calculate a hourly difference per dimensions

Stella_V — Tue, 24 Oct 2023 05:18:55 GMT

Hello

thank you very much for your answer.

I forgot to mention you that there are several different attributes in the column "Attribute 2" for example:

Hour 1 Forecast1

Hour 2 Forecast1

Hour 3 Forecast1

Hour 4 Forecast1

Hour 1 Forecast2

Hour 2 Forecast2

Hour 3 Forecast2

Hour 4 Forecast2

Hour 1 Forecast3

Hour 2 Forecast3

Hour 3 Forecast3

Hour 4 Forecast3

Should we add an additional filter on the Attribute2? What I need is the hourly difference between two countries, per forecast type.

thank you !!
Stella

Re: Calculate a hourly difference per dimensions

barritown — Tue, 24 Oct 2023 07:00:19 GMT

Stella_V,

This approach will work with multiple Forecasts (Forecast1, Forecast2, etc.) - I dare you to try. 🙂 Should you face any issues, let me know.

>>Should we add an additional filter on the Attribute2?

There is no need to apply additional filtering.

Cheers,

Alexander

Re: Calculate a hourly difference per dimensions

Stella_V — Tue, 24 Oct 2023 15:18:27 GMT

It works perfectly!!! thank you!!!!!