Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi. I'm trying to set up a number of standard formula to be used in my PBI reports.
users are supposed to select 1 year. Also they can select additional filters such as YTD and whatever you have .
In any case :
Asume selected is (only) 2022 in the date filter.
(This does not - Except then selecting both 2022 AND 2021)
Why does the following above not work? How do I override the 2022 selection?
Can anyone help me with this?
@mark77 , You need to use all
TEST_TY =
VAR __YEARSELECTION = CALCULATE(MAX('Date'[Year])-1)
VAR __BASEFORMULA = CALCULATE([Product SO],FILTER(all('Date'),'Date'[Year]=__YEARSELECTION))
RETURN
__BASEFORMULA
Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
Hi @amitchandak ,
Thank you for your response. Unfortunately this does not yield the required result. I had tested with all myself, but that will generate a grand total for me.
As you can see with the (modified) data here, the calculation falls apart in a table.
And I really do want to use a great many tables in many variants 😉
Do you know how I'd be able to resolve this ?
The Formula does respond correctly to filters though. For example a subcompany was selected here.
YTD_M | MonthName | Product SO | TEST1_self | TEST_withall |
Yes | October | 86,986 | 8,215,746 | |
Yes | November | 131,285 | 8,215,746 | |
Yes | December | 217,836 | 8,215,746 | |
Yes | January | 792,691 | 8,215,746 | |
Yes | February | 1,661,305 | 8,215,746 | |
Yes | March | 2,067,164 | 8,215,746 | |
Yes | April | 956,637 | 8,215,746 | |
No | May | 523,530 | 8,215,746 | |
No | June | 386,022 | 8,215,746 | |
No | July | 453,112 | 8,215,746 | |
No | August | 288,973 | 8,215,746 | |
No | September | 75,898 | 8,215,746 | |
TOTAL | 7,641,440 | 8,215,746 |
User | Count |
---|---|
47 | |
26 | |
21 | |
17 | |
15 |
User | Count |
---|---|
53 | |
34 | |
17 | |
17 | |
15 |