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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
kman42
Helper III
Helper III

USERELATIONSHIP and CALCULATE with other filters

I'm trying to get the following code to work, but I just get the same result for every fiscal year:

 

direct_cost_calc YTD = TOTALYTD(CALCULATE(SUM('award_table'[direct_cost_calc]),'Date'[Date]<=TODAY(), USERELATIONSHIP('Date'[Date],award_table(awed_period_beg_date])), 'Date'[Date],"6/30")

If I set the relationship manually through the Manage Relationships dialog and remove the CALCULATE function (just use the SUM function), it works fine. 

 

Thanks!

1 ACCEPTED SOLUTION
Anonymous
Not applicable

In the RETURN, replace this:

day_in_year

 with this:

'Date'[DayNoOfYear] <= day_in_year

 

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

@kman42 - 

There's a problems with:

'Date'[Date]<=TODAY()

That changes the Dates in context to all dates today or before, so the YTD shows this year's value.

One thing you could do, if you have the DayNumberOfYear in your Date table, is:

var day_in_year = CALCULATE(MAX('Date'[DayNumberOfYear]), 'Date'[Date] <=TODAY())

Then use that variable in place of the date filter.

 

Hope this helps,

Nathan

I'm still missing something. I tried:

 

direct_cost_calc YTD=
var day_in_year = CALCULATE(MAX('Date'[DayNoOfYear]), 'Date'{Date] <=TODAY()
RETURN
TOTALYTD(CALCULATE(SUM('award_table'[direct_cost_calc]),day_in_year, USERELATIONASHIP('Date'[Date],award_table[awrd_period_beg_date])),'Date'[Date],"6/30")

I get the following error:

The True/False expression does not specify a column. Each True/False experssions used as a table filter experssion must refer to exactly one column.

 

 

Anonymous
Not applicable

@kman42 -

The Date column needs square braces instead of curly:

'Date'{Date] 

 Does it work after changing that?

No. That was just me making a typo when I posted it.

Anonymous
Not applicable

In the RETURN, replace this:

day_in_year

 with this:

'Date'[DayNoOfYear] <= day_in_year

 

Sorry. I'm obviously missing something pretty basic. Here are my sparklines:

 

Screen Shot 2019-04-22 at 1.32.36 PM.png

 

The top one uses the following measure and the relationship was set manually:

 

Screen Shot 2019-04-22 at 1.32.28 PM.png

 

The bottom one uses the following measure:

 

Screen Shot 2019-04-22 at 1.32.01 PM.png

 

The top one is right, escept I want it filtered to before today. That tail is currently running out until 2024. I also want it to dynamically set the relationship as in the second measure so that I can have other measures on the table with other relationships.

 

Thanks for all your help!

Anonymous
Not applicable

You need to compare like attributes - you don't want to compare Date[Date] to day_in_year. You want this:

 

'Date'[DayNoOfYear] <= day_in_year 

 

Thank you! I finally got it with the following:

 

Screen Shot 2019-04-22 at 2.03.15 PM.png

 

I had to add the filter to eliminate the tail on the graph.

 

Thanks for all your help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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