Year To Max Date

[NewbieJono]

is there a method of doing a year to max date in a fact table.

i need the year to start 01/04 and I need to calculate volumes to the max date of another dateset.

i will also need to do a same period previous year on this metric.

[Sergii24]

You can add a calculated column "InScope" to you Calendar Table that will compare date of a current row with max date from forct table. Once you have it, you can make a desired calculation using this template code:

Measure1 = 
VAR _CurrentlySelectedMinDate = Min( CalendarTable[Date] )
   Calculate(
      Your_calcuation,
      CalendarTable[Date] >= _CurrentlySelectedMinDate,
      CalendarTable[InScope] = 1
   )

I hope it helps!

[NewbieJono]

how would i do the in scope calculation?

[Sergii24 · Accepted solution]

Like this:

InScope = 
VAR _MaxFromFactTable = MAX( YOUR_FACT_TABLE[Date] )
RETURN
    IF(
        'Calendar Table'[Date] <= _MaxFromFactTable,
        1,
        0
    )

Good luck! 🙂