Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a YTD chart that includes two lines. One line is 2017, the other is 2018. The problem is that I created a measure that gives a running total and it didn't stop at year's end. Check out the blue line below. Here are my measures:
**This one gets a count of employees whose end date is in the month. DateTable1 is my calendar. Masterall Turnover is the table. End date is a date column in my table.
YTD Trial 2017 = calculate(countrows('masterall turnover'), USERELATIONSHIP(DateTable1[Date],'masterall turnover'[end date]),
year('masterall turnover'[end date])=2017,
'masterall turnover'[termination type]<>"transfer" ,
'masterall turnover'[termination type]<>"Voluntary (Leave of Absence)",
'masterall turnover'[title]<> "non-billable" ,
'masterall turnover'[title]<> "bench"
)
**This gives the cumulative total from the previous measure. It is a YTD measure and it works but it doesn't stop at the end of the year.
YTD Trial 2017 running total in Date =
CALCULATE(
[YTD Trial 2017],
FILTER(
ALLSELECTED('DateTable1'[Date]),
ISONORAFTER('DateTable1'[Date], MAX('DateTable1'[Date]), DESC)
)
)
Solved! Go to Solution.
I used teh quick measure utility and dragged over monthshort instead of datetable[date}. It now looks like the year runs from jan-dec.
YTD Trial 2017 running total in MonthShort =
CALCULATE(
[YTD Trial 2017],
FILTER(
CALCULATETABLE(
SUMMARIZE('DateTable1', 'DateTable1'[MonthNum], 'DateTable1'[MonthShort]),
ALLSELECTED('DateTable1')
),
ISONORAFTER(
'DateTable1'[MonthNum], MAX('DateTable1'[MonthNum]), DESC,
'DateTable1'[MonthShort], MAX('DateTable1'[MonthShort]), DESC
)
)
)
Since you didn’t share us some sample data or your desired result. I’m not quite understand about your issue. What did you mean the YTD did not stop at year’s end? Please share us more detailed information. So that we can understand your requirement more clearly and make some tests.
By the way, as you said that the YTD didn’t stop at year’s end. Maybe you can add an end date in filter in your second measure.
Thanks,
Xi Jin.
See the blue line? IT should be at 1 or 2 in January then build up from there. The cumulative total carried on over from 2017 to 2018. So, instead of showing 1 or 2 in Jan and Feb., it is showing the cumulative total running from December. Here is a table that shows what I want. [YTD Trial 2017] is a count then, the next column [YTD Trial 2017 running total] should be summing up that count (which it does in March, April, etc..)
Here are the columns in my 'masterall turnover' table that I am referencing
Employee | Start Date | End Date
Jim 11/1/2017 12/1/2018
I used teh quick measure utility and dragged over monthshort instead of datetable[date}. It now looks like the year runs from jan-dec.
YTD Trial 2017 running total in MonthShort =
CALCULATE(
[YTD Trial 2017],
FILTER(
CALCULATETABLE(
SUMMARIZE('DateTable1', 'DateTable1'[MonthNum], 'DateTable1'[MonthShort]),
ALLSELECTED('DateTable1')
),
ISONORAFTER(
'DateTable1'[MonthNum], MAX('DateTable1'[MonthNum]), DESC,
'DateTable1'[MonthShort], MAX('DateTable1'[MonthShort]), DESC
)
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |