Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi reader,
I need some help but I actually don't know if my idea is possible in BI.
My report has a mini dashboard at the top that shows a row with the results per year. What I did was put the results colom as a value, put the year colom as a filter and select each different year based on the year I want the visual to filter/show.
Now the problem is that the customers data will of course change next year. I want the mini dashboard to change automatically.
If I use a card visual on this measure Jaren = YEAR(TODAY())-1 it will show me the year 2019. Doing Jaren = YEAR(TODAY())-2 will show me the year 2018 so on and so forth. Now when I use this measure as a filter on the results per year visual it shows me the total of all the years. As if BI doesn't filter the data based on the value of the measure.
Is it that it doesn't recognize it as a value that needs to filter the data? Should I use a different measure so that it takes the value of the measure, compares it to the year colom in the data and then filters the result colom?
Hopefully I described my situation and question clearly and somebody knows the solution.
Thanks in advance 😄
Solved! Go to Solution.
@ACI_Data , have tried time intelligence like
example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
@ACI_Data , have tried time intelligence like
example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
@amitchandak Thanks so much for your quick answer! Learned something new today that its Time Intelligence DAX that I need. In the end I used the DAX This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year]))) and it worked. It shows the results of the year I wanted 😄
The thing is, I have 3 subjects (result, revenue and maintenance) where I use a mini dashboard and each have 5 result/revenue/maintenance per year card visual. So in the end, to make the calculations, I need to make 15 of those measures for each subject and each year.
Is it not possible to use 1 measure for the year 2019 and use that measure as a filter for each result/revenue/maintenance per year card visual? This way I only need to make the measures for the years, instead of the measure that immediately filters the result colom based on the year.
Thanks in advance!!
User | Count |
---|---|
94 | |
78 | |
73 | |
64 | |
60 |
User | Count |
---|---|
106 | |
97 | |
76 | |
63 | |
61 |