Forum Discussion

JamesUhlenkamp's avatar
JamesUhlenkamp
Regular Visitor
5 years ago
Solved

Establishing academic year

I have a table which includes events that happened on single date. The date format is 5/9/2016, for example. I need to report that the event happened in AY2015-16.  The academic years run 7/1/yyyy-6/...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    JamesUhlenkamp 

    Please create the following calculate column to get the expected result.

     

    Year = IF([Date].[MonthNo]<=6&&[Date].[Day]<=31,
    CONCATENATE([Date].[Year]-1,CONCATENATE("-",RIGHT([Date].[Year],2))),
    CONCATENATE([Date].[Year],CONCATENATE("-",RIGHT([Date].[Year]+1,2))))

     

     

    Paul Zheng _ Community Support Team
    If this post helps, please Accept it as the solution to help the other members find it more quickly.

JamesUhlenkamp's avatar
JamesUhlenkamp
Regular Visitor
5 years ago

amitchandak, does this help?

 

Do I need to create a calendar table to define the displayed Desired Outcome column?  Note: in the example, all the dates are in May or December, but the reality is that different tables (such as retention or assessment) will have dates in every month.

  • Samarth_18's avatar
    Samarth_18
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi JamesUhlenkamp ,

     

    You can create a column with below code:-

    Column =
VAR years =
    YEAR ( 'Table (6)'[Graduation date] )
RETURN
    YEAR ( 'Table (6)'[Graduation date] ) - 1 & "-"
        & INT ( FORMAT ( 'Table (6)'[Graduation date], "YY" ) )

    output:-

     

    Thanks,

    Samarth