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

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 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
  • 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?