Combination Annualized/Static Data for One Bar Chart

[Anonymous]

I need a measure that will allow me to create a bar chart with three years (2018, 2019, 2020) with 2020 as an annualized measure and 2018/19 pulling from static data.  (I am thinking an IF function of some sort ie if 2020 then multiply by 52, else use static data)

[v-juanli-msft · Accepted solution]

Hi @Anonymous 

Based on my understanding, your table has complete data for 2017-2019, but for 2020, there is only some weeks' data.

So you want to use average weeks' data for 2020 to predict whole year's data

by mutilpying average week's data by numbers of weeks of 2020.

To say easier, it is like below:

average weeks' data= total counts of 2020/weeks which data is not blank

numbers of year 2020=53

final=average weeks' data*53

If so,please look at my measure below:

modify count =
VAR n_weeks =
    CALCULATE (
        DISTINCTCOUNT ( 'Calendar'[WeekNumberOfYear] ),
        FILTER (
            ALLSELECTED ( 'Calendar' ),
            'Calendar'[Year]
                = YEAR (
                    TODAY ()
                )
        )
    )
VAR sum_count =
    CALCULATE (
        COUNT ( Turnover[Departments Names] ),
        FILTER (
            ALLSELECTED ( Turnover ),
            Turnover[Year]
                = YEAR (
                    TODAY ()
                )
        )
    )
VAR counts_weeks =
    CALCULATE (
        DISTINCTCOUNT ( Turnover[Week Number] ),
        FILTER (
            ALLSELECTED ( Turnover ),
            Turnover[Year]
                = YEAR (
                    TODAY ()
                )
        )
    )
RETURN
    IF (
        MAX ( 'Calendar'[Year] )
            = YEAR (
                TODAY ()
            ),
        n_weeks
            * DIVIDE (
                sum_count,
                counts_weeks
            ),
        CALCULATE (
            DISTINCTCOUNT ( Turnover[Departments Names] ),
            FILTER (
                ALLSELECTED ( Turnover ),
                Turnover[Year]
                    = MAX ( Turnover[Year] )
            )
        )
    )

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

[amitchandak]

can you share some sample data.If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

Hello @amitchandak ,

Please see attached sample dataset and model.  To contextualize, I would like the bar representing 2020 to be an annualized amount while using the static data from 2018/19 to populate their corresponding visual categories. 

Thank you,

Sample Data and Model