Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
MarkCBB
Helper V
Helper V

Limit Calendar table to latest date of Fact table within M

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.

 

 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

This is prefect, thank you

Anonymous
Not applicable

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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors