Using Date Tables Properly

[EpicTriffid]

Hi all,

I have created a date table to map out academic years (Sept-Sept) based on actuals date (e.g. 01/01/2016 would be the 2015/16 Academic Year, whilst 01/09/2016 would be the 2016/17 Academic Year). An example of this is below:

I created this using this code:

Date Table = 
VAR MinYear =
    YEAR ( MIN ( Query2[DateYear] ) )
VAR MaxYear =
    YEAR ( MAX ( Query2[DateYear] ) )
RETURN
    ADDCOLUMNS (
        FILTER (
            CALENDARAUTO (),
            AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
        ),
        "Calendar Year", YEAR ( [Date] ),
        "Month Name", FORMAT ( [Date], "mmmm" ),
        "Month Number", MONTH ( [Date] ),
        "Academic Calendar Year", IF ( MONTH ( [Date] ) >= 9, YEAR ( [Date] ), YEAR ( [Date] ) - 1 ),
        "Academic Year", IF (
            MONTH ( [Date] ) >= 9,
            YEAR ( [Date] ) & "/"
                & RIGHT ( YEAR ( [Date] ) + 1, 2 ),
            YEAR ( [Date] ) - 1 & "/"
                & RIGHT ( YEAR ( [Date] ) , 2 )
                        ),
        "Academic Year (Short)", IF (
            MONTH ( [Date] ) >= 9,
            RIGHT ( YEAR ( [Date] ), 2 ) & "/"
                & RIGHT ( YEAR ( [Date] ) + 1, 2 ),
            RIGHT ( YEAR ( [Date] ) - 1, 2 ) & "/"
                & RIGHT ( YEAR ( [Date] ), 2 )
        )
    )

I have related this to my Query table using the Academic Year (Short) column in the Date Table, as that is what is in the Query table.

So now I need to create a measure that returns the data for the current academic year that we are in (2019/20). My thinking was that I could write something like:

CurrentYear = CALCULATE(
    SUM(Query2[Total Student Count]),
    YEAR('Date Table'[Date]) = YEAR(TODAY()))

But what i need it to do is to look across the Date Table, and instead return me the academic year:

So it would see the current date, but when filtering the Total Student Count, it would filter out those records for 19/20. I previously just did YEAR(TODAY()) -1, but this would break as soon as we hit September, which is what the date table resolves, if only i can get the CALCULATE to work. I think it might be something like RELATED or LOOKUPVALUE but I don't know how to string them together.

I'm hoping that I can use this new date table to use the time intelligence functions to sort out trending and the like.

I really hope you can help.

Cheers,

Matt

[v-alq-msft]

Hi, @EpicTriffid 

Based on your description, I created data to reproducce your scenario.

Table:

Date Table:

Date Table = 
VAR MinYear =
    YEAR ( MIN ( 'Table'[Date] ) )
VAR MaxYear =
    YEAR ( MAX ( 'Table'[Date] ) )
RETURN
    ADDCOLUMNS (
        FILTER (
            CALENDARAUTO (),
            AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
        ),
        "Calendar Year", YEAR ( [Date] ),
        "Month Name", FORMAT ( [Date], "mmmm" ),
        "Month Number", MONTH ( [Date] ),
        "Academic Calendar Year", IF ( MONTH ( [Date] ) >= 9, YEAR ( [Date] ), YEAR ( [Date] ) - 1 ),
        "Academic Year", IF (
            MONTH ( [Date] ) >= 9,
            YEAR ( [Date] ) & "/"
                & RIGHT ( YEAR ( [Date] ) + 1, 2 ),
            YEAR ( [Date] ) - 1 & "/"
                & RIGHT ( YEAR ( [Date] ) , 2 )
                        ),
        "Academic Year (Short)", IF (
            MONTH ( [Date] ) >= 9,
            RIGHT ( YEAR ( [Date] ), 2 ) & "/"
                & RIGHT ( YEAR ( [Date] ) + 1, 2 ),
            RIGHT ( YEAR ( [Date] ) - 1, 2 ) & "/"
                & RIGHT ( YEAR ( [Date] ), 2 )
        )
    )

There is a one-to-one relationship between two tables.

You may create a measure as below.

IsDisplay = 
var _academicyear = 
CALCULATETABLE(
    DISTINCT('Date Table'[Academic Year (Short)]),
    'Date Table'[Date] = TODAY()
)
var _currentvalue = SELECTEDVALUE('Date Table'[Academic Year (Short)])
return
IF(
    _currentvalue in _academicyear,
    1,0
)

Then you may put the measure in the visual level filter. Here is the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler · Accepted solution]

I'm thinking:

CurrentYear = 
  VAR __Year = LOOKUPVALUE('Date Table'[Academic Calendar Year],'Date Table'[Date],TODAY())
RETURN
CALCULATE(
    SUM(Query2[Total Student Count]),
    'Date Table'[Academic Calendar Year]) = __Year
)

Not sure you will be able to do time intelligence. See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[EpicTriffid]

Thank you. This worked great. 

Like you said, I don't think I can use Time Intelligence with this, mostly as I think the DAX required would kill me!

However, I did quite easily move it forward 1, 3 and 5 Years, and was able to subtract this from the Current Year to get a difference so I get some semblance of Time Intelligence:

1 Year Trend = 
VAR OneYearDate = LOOKUPVALUE('Date Table'[Academic Calendar Year],'Date Table'[Date],TODAY())
RETURN

VAR OneYear = 
CALCULATE( 
    SUM(Query2[Total Student Count]),
    'Date Table'[Academic Calendar Year] = OneYearDate + 1
)
RETURN

OneYear - [CurrentYear]