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

Need Help

8 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Community Champion
    6 years ago

    Hi BSLATTER , 

     

    Assuming that the table structure is similar to below.

     

    DateSurvey IDStatus
    01-01-20191Complete
    02-01-20192Complete
    03-01-20193Complete
    04-01-20194Complete

     

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

      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:

      DateSurvey IDStatus
      01-01-20191Complete
      02-01-20192Complete
      03-01-20193Complete
      04-01-20194Complete

       

      CalendarData:

      DateFiscal PeriodFiscal Year
      01-01-2019132018
      02-01-201912019
      03-01-201922019
      04-01-201932019

       

       

      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.