Forum Discussion

BSLATTER's avatar
BSLATTER
Helper III
6 years ago
Solved

Creating a YTD

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
BSLATTER
Helper III
6 years ago

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?

    • BSLATTER's avatar
      BSLATTER
      Helper III
      6 years ago

      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!