cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Anonymous
Not applicable

Re: Limit Calendar table to latest date of Fact table within M

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
Highlighted
Anonymous
Not applicable

Re: Limit Calendar table to latest date of Fact table within M

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

Highlighted
Anonymous
Not applicable

Re: Limit Calendar table to latest date of Fact table within M

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.

Highlighted
Helper V
Helper V

Re: Limit Calendar table to latest date of Fact table within M

This is prefect, thank you

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors