Forum Discussion

blairtj's avatar
blairtj
Regular Visitor
6 years ago

Measure result different values based on calculated column vs imported column

Hi,

 

I'm using covid 19 stats to get a handle on Power BI. I'm starting to get it, but this case so far is stumping me. The goal is getting a trend direction for daily new cases. I'm not a statitician so the techniques are just amature attempts at smoothing, but power bi is the goal.

 

I have a table with daily case and death counts for all countries. Before importing from excel I added a column that for each date record took the average cases for the last 4 days and subtracted from that the average of the previous 4 days for that country (I called this 4x4trend). In power bi I then created a calculated column to provide the same value (4x4trendcalc).

 

This 4x4trend is still quite volatile so my next attempt was to then to create a measure that averaged the last 7 days of the 4x4 trend. I called this 4x4trenddailyavg7.

 

 

 

4x4trenddailyavg7 = 
    VAR window = 7 /* days */
    VAR current_date = LASTDATE('covid-data'[dateRep])
    VAR window1_end = current_date
    VAR window1_start = DATEADD(window1_end,1-window,DAY)

    VAR window1_average = CALCULATE(
            IF(COUNTROWS(ALL('covid-data'[dateRep])) >= window,
                AVERAGEX('covid-data', 'covid-data'[4x4trend])
            ),
            FILTER(
                ALL('covid-data'[dateRep]), 
                'covid-data'[dateRep] >= window1_start && 'covid-data'[dateRep] <= window1_end 
            )
        )

    RETURN (
        window1_average
    )

 

 

 

This worked well but the goal is to only transform the data in power bi as the stats change daily and I just want to download and plug in the updated data. This measure relied on the excel calculated column so I tried the exact same measure  but just averaged the column 4x4trendcalc instead of the imported 4x4trend (I called 4x4trenddailyavg7calc). The value is completely different and wrong.

 

An example country can be seen below.

 

Any ideas as to why a measure over a calculated column produces a different (and incorrect) value than the same measure over a column with the values but that was imported?

 

 

Need Help

2 Replies

    • blairtj's avatar
      blairtj
      Regular Visitor
      6 years ago

      v-chuncz-msft, thank you for your reply.

       

      That is true of the first 8 day's data for each country. The data I'm concerned with for this is window of the last week roughly of the data and so I didn't bother in the source excel file correct the spill-over into the previous country's stats in the first few days. I still think that's true.

       

      I'll upload the pbix file if that helps. I still haven't found why this would be the case. The data comes from the publicly available data published on the European CDC site.

       

      Any help would be appreciated.