Limit Calendar table to latest date of Fact table within M

[MarkCBB]

Hi there,

I would like to limit my calendar table to the latest date of my fact table, in both table the date column is called "DATE" the table names are FACT, and CALENDAR.

[Anonymous · Accepted solution]

Hi Mark,

You can do this in several ways, below you find two.

If you use the dax formula =CALENDAR() to create the DATE calendar you could use below snippet te filter based on the maximum date in your fact.

DATE = 
VAR MaxDateFACT = MAX('FACT'[Date])
RETURN
CALENDAR("1-1-2017";MaxDateFACT)

If you imported the date table you could do this trick in M. I am no M expert so maybe it can be done easier, but this works 🙂

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtS10DUyMDRXitWJVjJC5hgjc0yQOabIHDNkjjkyxwKZYwnnxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    MaxDate = Record.Field(Table.Max(FACT,"Date"),"Date"),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= MaxDate )
in
  #"Filtered Rows"

The MaxDate holds the maximum value for the Date column in the FACT table. You can append this logic to your existing query by opening the "Advanced editor" for your Date table.

Hope this helps!

Regards

Jordi

[MarkCBB]

This is prefect, thank you

[Anonymous]

Forgot to mention in my previous post... be aware that this scenario only works when you have one fact table with a date dimension. When the data dimension is used by multiple facts this could result in not showing data when for example there is a week without sales but purchases have been done that week.