Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to 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.
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.
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...
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
75 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |