Re: Compare current period to the average of that ...

ciervax · ‎04-16-2021

Hi, I'm trying to compare a current period with the average of the same period of the last X years.

I have data from 2009 of several weather stations. The important columns are:

DATE (which is month,day and year), RAIN, WIND SPEED, TEMPERATURE

DATE	TEMP	WIND SPEED	RAIN
01/01/2009	12.26	0.84	0
01/02/2009	12.8	0.79	0
01/03/2009	11.54	0.49	1
01/04/2009	10.47	0.93	1.6
01/05/2009	9.07	0.81	0.03
01/06/2009	9.45	1.11	0
01/07/2009	6	1.48	0
01/08/2009	4.9	1.43	4.2
01/09/2009	6.49	1.11	0.6
01/10/2009	4.74	1.62	6.4
01/11/2009	9.74	1.3	5.8
01/12/2009	8.93	1.18	0
1/13/2009	9.11	1.9	0
1/14/2009	9.76	2.02	0
1/15/2009	8.25	1.51	0
1/16/2009	9.46	1.25	0

I've set a date report filter, like this one:

What I am trying to get, is a table per month, from 01/07/2020 till 16/04/2021. With the current values on each column of:

RAIN, WIND SPEED, TEMPERATURE, and the same number of columns with a comparing the current period with the average from 2009 on the same period.

For example, the RAIN Comparision column should be, compare the current period to the TOTAL average of:

01/07/2019 - 16/04/2020

01/07/2018 - 16/04/2019

01/07/2017 - 16/04/2018

01/07/2016 - 16/04/2017....

So I can get the % above or lower of the current period against the average of the total data I have, on the same period.

About temperature, I want to compare against the average of days below 3ºC and another column below 0ºC.

Wind speed I want to do similar to temperature, but, wind speed above: 11.1 and days above 15.2

I can share the full .csv , can I link it here directly? or is it forbidden?

Thank you in advance.

lbendlin · ‎04-20-2021

"Just to clarify, I need a column with the current period, and on the right side another column with the comparison of that period to the average of all years I have, except the current."

Now you are saying you need averages per month? Please restate your request with more detail and with full context.

v-yingjl · ‎04-19-2021

Hi @ciervax ,

Based on the sample data, the RAIN Comparsion measure could be like this:

RAIN Comparison = 
AVERAGEX (
    FILTER (
        ALL ( 'WEATHER' ),
        MONTH ( 'WEATHER'[DATE] ) >= 4
            && MONTH ( 'WEATHER'[DATE] ) <= 7
            && DAY ( 'WEATHER'[DATE] ) >= 1
            && DAY ( 'WEATHER'[DATE] ) <= 16
            && YEAR ( 'WEATHER'[DATE] )
                <= CALCULATE (
                    YEAR ( MIN ( 'Calendar'[Date] ) ),
                    ALLSELECTED ( 'Calendar'[Date] )
                )
    ),
    [RAIN]
)

By the way, the filter condition as your mentioned about temp and wind speed when I applied in the sample table, it looks like have no data match it so I created the simliar measure to calculate the average temporarily.

TEMP Comparsion = 
AVERAGEX (
    FILTER (
        ALL ( 'WEATHER' ),
        MONTH ( 'WEATHER'[DATE] ) >= 4
            && MONTH ( 'WEATHER'[DATE] ) <= 7
            && DAY ( 'WEATHER'[DATE] ) >= 1
            && DAY ( 'WEATHER'[DATE] ) <= 16
            && YEAR ( 'WEATHER'[DATE] )
                <= CALCULATE (
                    YEAR ( MIN ( 'Calendar'[Date] ) ),
                    ALLSELECTED ( 'Calendar'[Date] )
                )
    ),
    [TEMP]
)

Wind Comparsion = 
AVERAGEX (
    FILTER (
        ALL ( 'WEATHER' ),
        MONTH ( 'WEATHER'[DATE] ) >= 4
            && MONTH ( 'WEATHER'[DATE] ) <= 7
            && DAY ( 'WEATHER'[DATE] ) >= 1
            && DAY ( 'WEATHER'[DATE] ) <= 16
            && YEAR ( 'WEATHER'[DATE] )
                <= CALCULATE (
                    YEAR ( MIN ( 'Calendar'[Date] ) ),
                    ALLSELECTED ( 'Calendar'[Date] )
                )
    ),
    [WIND SPEED]
)

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ciervax · ‎04-20-2021

Thank you for your help.

The problem I see here is that for all months or days, the % is the same.

Example of your measure:

As you can see, for all ID1 and ID2 the value is the same 0.85, and in all months the values are the same 0.85.

Should be different per month and ID, and then the total average of that period on the bottom of the table.

thanks in advace.

v-yingjl · ‎04-21-2021

Hi @ciervax ,

Use Allexcept() to replace All() function in the measure like:

ALLEXCEPT ( 'WEATHER', [your ID column], [your month column] )

Use Hasonefilter() or Hasonevalue() to modify the total value, please refer: Dealing with Measure Totals

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ciervax · ‎04-22-2021

Thanks, Ying,

I've tried yesterday and today to solve the issue, this is what I have right now, focusing on RAIN column:

RAIN Comparison = 
AVERAGEX (
    FILTER (
        ALLEXCEPT ( 'WEATHER',WEATHER[ID1],WEATHER[Month] ),
        MONTH ( 'WEATHER'[DATE] ) >= 4
            && MONTH ( 'WEATHER'[DATE] ) <= 7
            && DAY ( 'WEATHER'[DATE] ) >= 1
            && DAY ( 'WEATHER'[DATE] ) <= 16
            && YEAR ( 'WEATHER'[DATE] )
                <= CALCULATE (
                    YEAR ( MIN ( 'Calendar'[Date] ) ),
                    ALLSELECTED ( 'Calendar'[Date] )
                )
    ),
    [RAIN]
)

Result:

I've tried the following:

RAIN Comparison = 
AVERAGEX (
    FILTER (
        ALLEXCEPT ( 'WEATHER',WEATHER[ID1],WEATHER[Month] ),
        MONTH ( 'WEATHER'[DATE] ) >= 4
            && MONTH ( 'WEATHER'[DATE] ) <= 7
            && DAY ( 'WEATHER'[DATE] ) >= 1
            && DAY ( 'WEATHER'[DATE] ) <= 16
            && YEAR ( 'WEATHER'[DATE] )
                <= CALCULATE (
                    YEAR ( MIN ( 'WEATHER'[DATE]) ),
                    ALLSELECTED ( 'WEATHER'[DATE] )
                )
    ),
    [RAIN]
)

But still no luck, I am not able to get in column rain, the rain of the current period, and RAIN comparison difference between the current period vs average of the last X years.

This is my target but with the correct numbers on the column "RAIN vsXy"

Thanks in advance, really appreciate your help.

lbendlin · ‎04-16-2021

please go ahead and share the .csv

In your example the period stretches over two years. How do you then define "the same period in 2009" ?

ciervax · ‎04-16-2021

Because my data starts 01/01/2009, the first period should be: 01/07/2009 - 16/04/2010.

Just to clarify, I need a column with the current period, and on the right side another column with the comparison of that period to the average of all years I have, except the current.

the csv: