Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BSLATTER
Helper III
Helper III

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?

1 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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

8 REPLIES 8
nandukrishnavs
Super User
Super User

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


Regards,
Nandu Krishna

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.

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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?

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors