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
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).
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.
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.
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.
User | Count |
---|---|
30 | |
17 | |
15 | |
14 | |
10 |