Dynamic use of MAX date of a fact tabe

[Anonymous]

In my PBI report I created a Date_Calendar table using DAX:

                  Date_Calendar =

                                    GENERATE (      

                                     CALENDAR(Date(2019,1,1),Date(2021,12,31)),

                                    Etc…

Then I mapped Date_Calendar[Date] to the Date fields in 2 fact tables. However, currently, data from the 2 fact tables do not go beyond July 2020, whereas the end date for the Date_Calendar table is set to Dec 2021.

How can I create a column or measure such that it will always be the latest available date in the PTO fact table, and such that I can use that new field/column to filter on the Date_Calendar[Date] slicer so that no one can choose a date/month that has no data, since the latest date they can choose is the date which has the latest available date.

Hope my question makes sense. Thanks!

[mahoneypat]

You can use dynamic expression inside Calendar( ).  Like this - Calendar(Min(PTO[Date]), Max(PTO[Date]))

You can also do start and end of month calculations if you prefer.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

It's not letting me. See screenshot. 

PTO is a table of start and end dates of PTO that employees requested.

So, I actually want the min date of Start Date and max date of End Date, to make sure that I capture the widest range of dates available. The dax measure below I tried to put in my Date_Calendar table, but I've never referenced the Date field of other tables.

[mahoneypat · Accepted solution]

That expression is to create a new DAX table, not a measure.  Replace your hard-coded Date values in your current Date table with the dynamic ones.

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Anonymous]

I am missing one field from the old Calendar_Date table that I need. And that is the 'day' field.

i.e. Monday, Tuesday, etc.

Can you tell me how to create 'day' in this new Date table? Thanks.

[Anonymous]

That worked. Thanks!