Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a calculated table with 5 column, and I need to add the 6th one with the number of Days YTD, my table would look like this:
Year School Year Star Date End Date Total Days Days YTD
2015 2014-2015 8/25/2014 6/3/2015 177 113
2016 2015-2016 8/24/2015 6/2/2016 177 114
2017 2016-2017 8/22/2016 6/1/2017 178 116
I am using this expression:
School Years = GROUPBY(Dates, Dates[Year], Dates[School Year], "Start Date", MINX( CURRENTGROUP(), Dates[CalendarDate] ), "End Date", MAXX( CURRENTGROUP(), Dates[CalendarDate] ), "Total Days", COUNTX(CURRENTGROUP(), Dates[CalendarDate]), "Days YTD", COUNTX(CURRENTGROUP(), DATESYTD(Dates[CalendarDate])))
I get this error:
The CALCULATETABLE function cannot be used in an expression argumane for the GROUPBY() function.
How can I achieve what I intend?
Thans,
Solved! Go to Solution.
HI @Anonymous
This calculated table might be close. Give it a try and let me know what you think.
New Table = SUMMARIZECOLUMNS( 'SchoolDays'[Year], 'SchoolDays'[SY], "Start Date" , MIN('SchoolDays'[CalendarDate]), "End Date" , MAX('SchoolDays'[CalendarDate]), "Total Days" , COUNTROWS('SchoolDays'), "Days YTD" , CALCULATE( COUNTROWS('SchoolDays'), FILTER(ALL('SchoolDays'), 'SchoolDays'[Year] = MAX('SchoolDays'[Year]) && 'SchoolDays'[CalendarDate] < DATE( IF(MONTH(TODAY()) < 8, MAX('SchoolDays'[Year], MAX('SchoolDays'[Year])-1) ), MONTH(TODAY()), DAY(TODAY()) ) ) ) )
Any chance you can share a small sample of your raw data?
My Raw Dates table's data looks like this (SY=School Year, there are two semesters and 4 Terms in the SY):
CalendarDate SYDayNumber SYWeekNumber SYTermNumber SYSemesterNumber SY Year Month
8/25/2014 1 1 1 1 2014-2015 2015 8 (First Day)
8/26/2014 2 1 1 1 2014-2015 2015 8
8/27/2014 3 1 1 1 2014-2015 2015 8
.
.
9/8/2014 10 3 1 1 2014-2015 2015 9
.
.
10/22/2014 41 9 2 1 2014-2015 2015 8
.
6/3/2015 177 41 4 2 2014-2015 2015 6 (Last Day)
8/24/2015 1 1 1 1 2015-2016 2016 8 (First Day)
.
.
HI @Anonymous
This calculated table might be close. Give it a try and let me know what you think.
New Table = SUMMARIZECOLUMNS( 'SchoolDays'[Year], 'SchoolDays'[SY], "Start Date" , MIN('SchoolDays'[CalendarDate]), "End Date" , MAX('SchoolDays'[CalendarDate]), "Total Days" , COUNTROWS('SchoolDays'), "Days YTD" , CALCULATE( COUNTROWS('SchoolDays'), FILTER(ALL('SchoolDays'), 'SchoolDays'[Year] = MAX('SchoolDays'[Year]) && 'SchoolDays'[CalendarDate] < DATE( IF(MONTH(TODAY()) < 8, MAX('SchoolDays'[Year], MAX('SchoolDays'[Year])-1) ), MONTH(TODAY()), DAY(TODAY()) ) ) ) )
Thanks, It works, I don't understand yet how, but it does what I needed.
Hi @Anonymous,
What do the numbers in Days YTD actually represent? Why is 2015 = 113, while 2016 = 114?
Cheers,
Phil
Hi Phil.
Those numbers represent the number of school days, as of today, since the beginning of the school year.
For instance, this School Year (2017), started on 8/22/2016, as of today (3/13), 116 School Days have passed. This same day last year, 114 school days had passed.
If this is not enough information I can certainly share some of the raw data.
Thanks,
Hi @Anonymous
And is a school day any Mon to Fri? Or does this count other holidays?
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |