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.
Hi,
I hope someone can help me out here 🙂
I have a graph which shows 2020 Revenue trend per month and another line which is 2017 - 2019 AVG, so you can see how much we differ from the trend and also what we can expect for the rest of the year based on history.
Below that there is another one which shows the same but showing the yearly totals.
I would like to 2017 - 2019 AVG line to dynamicly calculate based on the selection in the table below. So if you only click on 2019 then it basically compares 2019 to 2020, if you select 2018 + 2019 then it only devides thes SUM by 2 and not 3.
For this I wrote a measure to count the selected years (and exclude 2020):
Selected years count = IF(CONTAINS(FinancialData , FinancialData[Date].Year] , 2020)=TRUE,
CALCULATE(DISTINCTCOUNT(FinancialData[Date].[Year]) , FinancialData[Date])-1,
CALCULATE(DISTINCTCOUNT(FinancialData[Date].[Year]) , FinancialData[Date]))
@OliverMatsik , Try like this with a date table
Avg last 3 year = if(isbalnk(SUM(FinancialData[2017 Revenue]),(CALCULATE(SUM(FinancialData[2017 Revenue]),DATESMTD(dateadd('Date'[Date],-1,year)))+CALCULATE(SUM(FinancialData[2017 Revenue]),DATESMTD(dateadd('Date'[Date],-2,year))) +CALCULATE(SUM(FinancialData[2017 Revenue]),DATESMTD(dateadd('Date'[Date],-3,year))))/3)
Thanks, I created a date table, but it somehow messed up my current date information, my graphs crashed.
Can you advise how can I make my calculation work maybe? If the calculated measure works well, it calculates the number of selected years correctly, why cant I use this number to simply divide the selected years' totals with the number of years?
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 |
---|---|
49 | |
25 | |
20 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |