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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
domtrump
Helper II
Helper II

dynamic date table based on min and max of date column

I would like to create a date table that is based on the customer's "Sign-up Date." As I do not wish to have dates in my pivot table from before the first sign-up or after the last sign-up, I want the date table to be dynamic and grow as more dates are added. I could certainly "hard code" the start date of the table since it would coincide with the company's opening, but I need the end date to go to the last sign-up date and not show anything in the future. How can I create a Date Table that grows based on the MAX date found in the "Sign-up" column?

 

Note: I typically just use a linked table from Excel for my date tables in PowerPivot but this has proven problematic as I need to include ALL dates between min and max sign-up in my columns and rows even if there is no data found for them when I slice the data (i.e the resulting pivot table needs to have the same number of columns and rows regardless of the slicers applied).

3 REPLIES 3
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

One of many ways to achieve this is to create a calendar table by wring DAX formula.

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1685417854972.png

 

 

Calendar = 
VAR _mindate =
    MIN ( Customer[Sign-up Date] )
VAR _maxdate =
    MAX ( Customer[Sign-up Date] )
RETURN
    ADDCOLUMNS (
        CALENDAR ( _mindate, _maxdate ),
        "Year", YEAR ( [Date] ),
        "Year-Month", FORMAT ( [Date], "yyyy-mmm" ),
        "Year-Month sort", EOMONTH ( [Date], 0 )
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Looks like that would do it, but where do I place this formula?

Does this just make a virtual table that you can use in formulas? How can I make a physical table that exhibits this behavior? Or is using a virtual table the way to go? Either way, I'm not sure where to place this formula in my PP Data Model.

Helpful resources

Announcements
October NL Carousel

Fabric Community Update - October 2024

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

Top Solution Authors
Top Kudoed Authors