Power bi desktop DAX table
Budget: €30 – €250 EUR
I have a .csv file with weather data from different regions.
I need to compare a specific period with the average of that period.
On the .csv file we have:
IdProvincia IdEstacion Fecha Año Dia Temp Media (ºC) Temp Max (ºC) Temp Mínima (ºC) Humedad Media (%) Humedad Max (%) Humedad Min (%) Velviento (m/s) VelVientoMax (m/s) Radiación (MJ/m2) Precipitación (mm) PePMon
We need "IdProvincia" to set a list, for instances 3="Region1", 12="Region2"....
On IdEstación, we need to show it on the power bi as: 10="City1", 9="City2"....
I want to be able to set the name of each number in IdProvincia and IdEstacion.
That's one part.
The second part is, on power BI I want to be able to choose the period for instances:
From 01/08/2019 to 31/07/2020.
And this should show a table where all months are shown, you can see the sample excel file to see the structure.
The important part here is to compare the period chosen with the average of that period in the previous year.
So August 2019 has to be compared to the average from existing data of August till 2018 included.
So if in August 2019 rain is: 15, and the average of August 2009,2010,2011,2012... is: 10, on the column:
Rain should be shown: 15
Column %Rain YoY should show the difference in % of 10 and 15: 50%
Same with avg temp, etc.
The different calculation comes in "Max wind count" , since I want to count in that month how many times the max wind has been more than 11. And the column "% Max wind YoY", has to be the avg of max wind count in the previous year with a value of more than 11.
In min temp, we have to do the same and count how many times the temp have been below 3.
Is important to have the following point into account,
If I compare this period:
From 01/08/2019 to 15/07/2020.
The average to compare should be the first 15 days of July, since we have data: 2009,2010,2011....
You have to sum all data from 2009, 2010... of the first 15 days of July, have as a result the average, and then compare it to the current period we set on the filter 15/07/2020.
***
Temp Media (ºC) = Avg temp
Precipitación (mm) = Rain
Humedad Media (%) = Avg humidity
Temp Mínima (ºC) = Min temp (COUNT below 3)
VelVientoMax (m/s) = Max wind count (COUNT above 11)
Radiación (MJ/m2) = Radiation
I need to compare a specific period with the average of that period.
On the .csv file we have:
IdProvincia IdEstacion Fecha Año Dia Temp Media (ºC) Temp Max (ºC) Temp Mínima (ºC) Humedad Media (%) Humedad Max (%) Humedad Min (%) Velviento (m/s) VelVientoMax (m/s) Radiación (MJ/m2) Precipitación (mm) PePMon
We need "IdProvincia" to set a list, for instances 3="Region1", 12="Region2"....
On IdEstación, we need to show it on the power bi as: 10="City1", 9="City2"....
I want to be able to set the name of each number in IdProvincia and IdEstacion.
That's one part.
The second part is, on power BI I want to be able to choose the period for instances:
From 01/08/2019 to 31/07/2020.
And this should show a table where all months are shown, you can see the sample excel file to see the structure.
The important part here is to compare the period chosen with the average of that period in the previous year.
So August 2019 has to be compared to the average from existing data of August till 2018 included.
So if in August 2019 rain is: 15, and the average of August 2009,2010,2011,2012... is: 10, on the column:
Rain should be shown: 15
Column %Rain YoY should show the difference in % of 10 and 15: 50%
Same with avg temp, etc.
The different calculation comes in "Max wind count" , since I want to count in that month how many times the max wind has been more than 11. And the column "% Max wind YoY", has to be the avg of max wind count in the previous year with a value of more than 11.
In min temp, we have to do the same and count how many times the temp have been below 3.
Is important to have the following point into account,
If I compare this period:
From 01/08/2019 to 15/07/2020.
The average to compare should be the first 15 days of July, since we have data: 2009,2010,2011....
You have to sum all data from 2009, 2010... of the first 15 days of July, have as a result the average, and then compare it to the current period we set on the filter 15/07/2020.
***
Temp Media (ºC) = Avg temp
Precipitación (mm) = Rain
Humedad Media (%) = Avg humidity
Temp Mínima (ºC) = Min temp (COUNT below 3)
VelVientoMax (m/s) = Max wind count (COUNT above 11)
Radiación (MJ/m2) = Radiation