First day of the Academic Year

[ngaray]

Hello,

I am trying to use the measure Year to Date to calculate the count of records (cases) of my dataset by year. My problem is that the first day of the Academic Year is always different (unlike Fiscal Year that starts on 7/1 or Calendar Year that starts on 1/1). The Academic Year starts the Monday of Week 33 of the year (it could be 8/13, 8/14, 8/15.....). Given that my first day of the academic year is not static, I am unable to use the measure below

Cases YTD = Calculate (
                                      COUNT('FactTable'[Cases]),

                                      DATESYTD('DateTable'[Date],"08/14"))

If you are familiar with the expression DATESYTD, you know I have to hard key "08/14". However, this is incorrect as 08/15 is not necessarily the first day of the academic year. If instead, I could use a variable like the one below, I think the Cases YTD expression would work.

Start of Week 33 = CALCULATE(

                                                   MIN('Date'[Start of the Week]),

                                                   'Date'[Week of Year] = 33)

Unfortunately, I can't use a variable for the second expression of DATESYTD. At this point, I think I need to completely rethink the way I am approaching this problem, however, I am not sure how to do this. Does anyone have any ideas?

I appreciate any help,

Nathalia

[v-piga-msft]

Hi @ngaray ,

For your scenario, we may could try with create the YTD without use DATESYTD function. We could have a reference of creating Cumulative to create the measure with CALCULATE and FILTER.

Please refer to the formula below.

Measure2 =
IF (
    SELECTEDVALUE ( 'Table'[Weeknum] ) * 100
        + SELECTEDVALUE ( 'Table'[weekday] ) >= 3301,
    CALCULATE (
        SUM ( 'Sheet15'[Sales] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Date] <= MAX ( 'Table'[Date] )
                && ( 'Table'[Weeknum] * 100 + 'Table'[weekday] ) >= 3301
                && YEAR ( 'Table'[Date] ) = YEAR ( MAX ( 'Table'[Date] ) )
        )
    ),
    BLANK ()
)

More details, please refer to my attachment.

Best  Regards,

Cherry

[ngaray]

Hi @v-piga-msft,

Thank you so much for your response! I really appreciate you sending a PBIX file, that really helped!

Unfortunately, there is a problem with the measure, it calculates the sum of sales from the first day of week 33 (3301) until December 31st (5301) of the same year. It doesn't calculate the cumulative sales after December, however since the Academic Year goes from mid-August to mid-August, I really need the cumulative sum after December.


With my limited understanding of DAX, I believe the issue is here SELECTEDVALUE('Table'[Weeknum])*100+SELECTEDVALUE('Table'[weekday])>=3301. However I am not sure how to fix it.

Thank you so much for your assistance,

Nathalia