cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
thebigwhite
Helper I
Helper I

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 IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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 IV
Super User IV

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors