Creating a YTD

[BSLATTER]

Hello,

I have a calculation for a Response Rate on a survey.

It is defined as

Response Rate = DISTINCTCOUNTNOBLANK(CompletedSurveyIDs)/DISTINCTCOUNTNOBLANK(SurveyIDs)

When I use this in a regular "Card" it works fine.

My dashboard consists of a card (with Response Rate) and a bar chart broken out by our Fiscal Periods (a financial calendar).

Right now clicking the bar chart filters the card on the Fiscal Period. It has been requested to make the card a "year to date". So for example if they select Period 7 in the bar chart, it would show the response rate when Period <= Period 7.

To do this I tried:

Response Rate YTD = CALCULATE([Response Rate], FILTER('Calendar', 'Calendar'[Period] <= MAX('Calendar'[Period])))

However my numbers appear to be really blown out. I notice that the icon next to the YTD calculation no longer appears to be a column, and it is trying to aggregate the numbers. When I select "Don't Aggregate" I see it creates several rows.

Any idea what's happening here? I think it may be an issue where I'm doing a calculate on an already aggregated measure? Perhaps an issue with my using DISTINCTCOUNTNOBLANK?

[nandukrishnavs]

Hi @BSLATTER , 

Assuming that the table structure is similar to below.

Date	Survey ID	Status
01-01-2019	1	Complete
02-01-2019	2	Complete
03-01-2019	3	Complete
04-01-2019	4	Complete

Here is the Response rate measure -

Response Rate YTD =
VAR completedCount =
    TOTALYTD (
        DISTINCTCOUNTNOBLANK ( Survey[Survey ID] ),
        Survey[Date].[Date],
        Survey[Status] = "Complete"
    )
VAR totalSurvey =
    TOTALYTD ( DISTINCTCOUNTNOBLANK ( Survey[Survey ID] ), Survey[Date].[Date] )
VAR rate = completedCount / totalSurvey
RETURN
    rate

Let me know if it is not working. If possible, please share the sample table with few data for reference. 

Regards,

Nandu Krishna

[BSLATTER]

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.

[v-shex-msft · Accepted solution]

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)  

Understanding DAX Auto-Exist 

Regards,

Xiaoxin Sheng

[BSLATTER]

Thanks @v-shex-msft - 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 tried

Response Rate YTD =

CALCULATE(DIVIDE([Responded YTD], [Total Surveys YTD],0), FILTER(ALL ( 'Calendar' ), 'Calendar'[Period] <= MAX('Calendar'[Period])))

And got the same result

[v-shex-msft]

HI @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

[BSLATTER]

Thank you @v-shex-msft  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?

[v-shex-msft]

HI @BSLATTER ,

I think it may be related to filter conflict, can you please provide pbix file with some dummy data for a further test? 

Managing “all” functions in DAX: ALL, ALLSELECTED, ALLNOBLANKROW, ALLEXCEPT 
Regards,

Xiaoxin Sheng

[BSLATTER]

Hi, thanks for all the help.

I think this has been resolved. Looks like my measure was actually built as a column. Though I thought it was built as a measure. I discovered this while building the sample data.

Going to assume that was just my mistake in selecting "new column" instead of "new measure" like I thought I did. My other calculations set up as measures are working and now my new one does as well.

Thank you for your patience!