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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
thebigwhite
Helper II
Helper II

Loading a table based on Min / Max from another Table

Good morning, 

I would like to have some tips on this scenario: 

 

I need to load a calendar table based on a time range interval which is coresponding to the Max And Min Date of another table. 

How you would manage it?

Thanks 

 

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@thebigwhite , Based on what I got

You can try like

Date =calendar(Min(Table[Date]),Max(Table[Date]))

 

or date =calendarauto()

You can use addcolumns on top of it add other calendar columns


https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos.

View solution in original post

2 REPLIES 2
swise001
Continued Contributor
Continued Contributor

@thebigwhite 

 

Are you trying to build a calendar table from scratch based on the date range in your 'fact' table?

 

There are a lot of ways to do this:  Here's one way: 

 

1.  Extract the MAX and MIN dates from a dulicate of your FACT Table: 
Use the Group By function and extract Max and Min from the date column

swise001_0-1600429480428.png

Then add two custom columns that identify the start of the month and end of the month for your date range. 

EndDate = Date.EndOfMonth([Max Date])
StartDate = Date.StartofMonth([Min Date])

swise001_1-1600429661820.png

 

Then use this formula to make a list of all the days between your start date and end date

{Number.From([StartDate])..Number.From([EndDate])}

 

Remove all the other columns - and expand the list

swise001_2-1600429751453.png

Change this to Date Format - and you are good to go!  

Add whatever extra columns you want to make this a true date table: 

 

amitchandak
Super User
Super User

@thebigwhite , Based on what I got

You can try like

Date =calendar(Min(Table[Date]),Max(Table[Date]))

 

or date =calendarauto()

You can use addcolumns on top of it add other calendar columns


https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

Appreciate your Kudos.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.