Forum Discussion

bobam's avatar
bobam
Frequent Visitor
5 years ago
Solved

Yearly average

Hi,   few questions. 1.  How to calculate yearly average with monthly values (as in table 1) where data months after 09 doesnt exit in data ? - so in data table exists only values from 01-09 mont...
  • mahoneypat's avatar
    5 years ago

    You can do this without creating additional tables, doing it all in your measure with a virtual table.

     

    Assuming you have a Date table with a YearMonth column for all months (including those w/o data yet), something like this should work.  This assumes you will make a visual that does not include the YearMonth column but has a filter on it limiting it to one year.  The first IF() puts a 100 in your calculation if your measure is blank for that YearMonth.  The second IF() returns a blank if Your Measure is blank for that YearMonth (i.e., not show Oct-Dec in your example).

     

    Year Avg =
    VAR summary =
        ADDCOLUMNS (
            DISTINCT ( Date[YearMonth] ),
            "@result",
                VAR result = [Your Measure]
                RETURN
                    IF (
                        ISBLANK ( result ),
                        100,
                        result
                    )
        )
    RETURN
        IF (
            ISBLANK ( [Your Measure] ),
            BLANK (),
            AVERAGEX (
                summary,
                [@result]
            )
        )

     

    Regards,

    Pat