Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
In the RETURN, replace this:
day_in_year
with this:
'Date'[DayNoOfYear] <= day_in_year
@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.
@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.
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:
The top one uses the following measure and the relationship was set manually:
The bottom one uses the following measure:
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!
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:
I had to add the filter to eliminate the tail on the graph.
Thanks for all your help!
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |