Forum Discussion
Creating a YTD
HI BSLATTER ,
If you want to achieve specific filter effect in your formula, I'd like to suggest you use ALL function to ignore filter effect on your table and DATE function to manually filter on that table records.
Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng
Hi,
Thank you - the issue is I would like this to return a YTD value when the Fiscal Period is being filtered by another visual.
Sample tables:
SurveyData:
| Date | Survey ID | Status |
| 01-01-2019 | 1 | Complete |
| 02-01-2019 | 2 | Complete |
| 03-01-2019 | 3 | Complete |
| 04-01-2019 | 4 | Complete |
CalendarData:
| Date | Fiscal Period | Fiscal Year |
| 01-01-2019 | 13 | 2018 |
| 02-01-2019 | 1 | 2019 |
| 03-01-2019 | 2 | 2019 |
| 04-01-2019 | 3 | 2019 |
My YTD is based on the "Fiscal Year" YTD. My "CalendarData" table has a row for every day of every year with what FP/FY it is in.
These two tables are joined on Completed Date = Calendar Date. One-To-Many join to the surveys from the calendar.
Now in my PowerBI report I created the Response Rate metric I described in first post
I then put it in a bar chart, with Fiscal Period as the X axis. That gives me my response rate per period. Which is good
I then put it in a card, which sums up to my YTD. Which is good
Now my users interact with the bar chart. And select say "Period 4". What I hope to do is have the card show YTD up to (and including) Period 4. Instead it filters everything down to Period 4. Which is expected but not what I'm looking to do.
However when I tried what I attempted in my first post to change this behaviour, it is doing something I do not expect at all. I'm at a bit of a loss.
HI BSLATTER ,
If you want to achieve specific filter effect in your formula, I'd like to suggest you use ALL function to ignore filter effect on your table and DATE function to manually filter on that table records.
Time Intelligence "The Hard Way" (TITHW)
Regards,
Xiaoxin Sheng
Thanks Anonymous - this helped a lot.
I created three calculations:
Responded YTD =
CALCULATE([Responded], FILTER(ALL ( 'Calendar' ), 'Calendar'[Period] <= MAX('Calendar'[Period])))Total Surveys YTD =
CALCULATE([Total Surveys], FILTER(ALL ( 'Calendar' ), 'Calendar'[Period] <= MAX('Calendar'[Period])))
These two work great but when I try:
Response Rate YTD =
DIVIDE([Responded YTD], [Total Surveys YTD],0)I am getting weird values. It looks like it is just returning the total number of the original field Responded, based on the period I select. Any ideas?I also triedResponse Rate YTD =CALCULATE(DIVIDE([Responded YTD], [Total Surveys YTD],0), FILTER(ALL ( 'Calendar' ), 'Calendar'[Period] <= MAX('Calendar'[Period])))And got the same resultHI BSLATTER ,
Please add all functions on SurveyData table, if you add to the calendar table, it will also work on max(Calendar[Date]) so it can only get the static value. (logic: looping whole survey data records to find out records who less than or equal to current calendar date)
Responded YTD = CALCULATE([Responded], FILTER(ALL ( 'SurveyData' ), 'SurveyData'[Date] <= MAX('Calendar'[Date]))) Total Surveys YTD = CALCULATE([Total Surveys], FILTER(ALL ( 'SurveyData' ), 'SurveyData'[Date] <= MAX('Calendar'[Date])))Regards,
Xiaoxin Sheng
Thank you Anonymous for the tip, I've attempted these calculations.
They do provide the correct results. IE: 35 responded out of 38 total for period 2 YTD, a summation of P1 (10 and 11) and P2 (25 and 27). This so far is the same as my original calculations.
Now when I attempt to do
Response Rate YTD = DIVIDE([Responded YTD],[Completed Surveys YTD],0)
I get the same issue I've been having, where it just returns the number of Responded for the current period I've selected when I put it in a row card. Any idea what I'm doing wrong?