Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

DATESYTD in GROUPBY

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  ...
  • Phil_Seamark's avatar
    Phil_Seamark
    9 years ago

    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())
                            )
                        )       
                    )
                )
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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())
                            )
                        )       
                    )
                )
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Thanks, It works, I don't understand yet how, but it does what I needed.

 

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago
    I'm happy to explain any part you need.