Calculating the INDEX of the last period in Fact Table

[Lightice83]

Hi,

Trying to write a function that will return the INDEX column (from the dimension table) for the last period (custom period) in the fact table

So for the below example the last period in the fact table is '2022 - ON4'. This has an INDEX of 24 per the dimension table

I was thinking of using related but couldn't work it out. Is it easier to bring the INDEX into the fact table & then just do a MAX with ALL?

Thanks

FACT Table

Dimension Table

[Mikelytics]

Hi,

Can you please share your data model because RELATED() should be a valid way. Is the filter direction set from the dimension table to the fact table? Is the date dimension key column distinct?

Best regards

Michael

Best regards

Michael

-----------------------------------------------------

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

@ me in replies or I'll lose your thread.

[Lightice83]

Thanks @Mikelytics yes it is a standard 1 to many relationship. I just don't know the syntax/function to for a MAX with Related?

[Mikelytics]

@Lightice83 

Are you looking for a measure or a calculated column. Can you please share want your intended outcome is? Maybe an example? What do you mean by custom period?

[Lightice83]

Sorry @Mikelytics 

Custom Period is the '2022 - ON4' I highlighted above. This are the periods I am using but they are not date fields hence I am using an INDEX column to determine which period is greater than the other (See the Dimension table above) but I have now brought that INDEX from the Dimension table into the Fact Table as a Custom Column

I'm looking for a measure to return 24 (being the INDEX of the last period in the Fact table for CODE 'GC036' which is Period '2022 - TP4')

I'm trying to return this measure into a Pivot table with all Periods for GC036  (I'm just using Power PIVOT atm) but currently it is returning 25 because that is the MAX period for all Programs in the Fact Table

Fact Table

Output

CALCULATE(MAX(IH_CAC_Data[INDEX]),
		ALLEXCEPT(IH_CAC_Data,IH_CAC_Data[CODE]))

[Lightice83 · Accepted solution]

I solved it @Mikelytics  but many thanks for your help

I needed to have the correct ALL statement, that is to remove the Filter context for the Periods & then the MAX calculation could perform across all Periods but still maintain the filter for the Program/CODE