Forum Discussion
Compare Daily Values to their respective Monthly average
Hi I have a simple table with daily weather data, along with a standard date table.
I want to put up a matrix or table visual where I list daily temperature value in one column and average value for that month in another column.
Matrix Visual
Hi Anonymous ,
You can try to use the following measure formulas if they meet for your requirement:
Daily = VAR currDate = MAX ( table[Date] ) RETURN CALCULATE ( SUM ( table[Amount] ), FILTER ( ALLSELECTED ( Table ), [Date] = currDate ) ) Monthly AVG = daily = VAR currDate = MAX ( table[Date] ) RETURN CALCULATE ( AVERAGE ( table[Amount] ), FILTER ( ALLSELECTED ( Table ), YEAR ( [Date] ) = YEAR ( currDate ) && MONTH ( [Date] ) = MONTH ( currDate ) ) )Regards,
Xiaoxin Sheng
2 Replies
Hi Anonymous ,
You can try to use the following measure formulas if they meet for your requirement:
Daily = VAR currDate = MAX ( table[Date] ) RETURN CALCULATE ( SUM ( table[Amount] ), FILTER ( ALLSELECTED ( Table ), [Date] = currDate ) ) Monthly AVG = daily = VAR currDate = MAX ( table[Date] ) RETURN CALCULATE ( AVERAGE ( table[Amount] ), FILTER ( ALLSELECTED ( Table ), YEAR ( [Date] ) = YEAR ( currDate ) && MONTH ( [Date] ) = MONTH ( currDate ) ) )Regards,
Xiaoxin Sheng