Forum Discussion
Calendar function dependent on 2 date columns
Hi, I have a Date table in my data model where I need to use the Calendar function for the date column based on 2 date columns (ENR_DATE and ADM_DATE) from the fact table. I am currently using ...
Hi Pdawar from original reference link https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Adjust code part Sales[Order Date] to your columns . Hope this help
Date =
VAR MinYear = YEAR ( MIN ( Sales[Order Date] ) )
VAR MaxYear = YEAR ( MAX ( Sales[Order Date] ) )
RETURN
ADDCOLUMNS (
FILTER (
CALENDARAUTO( ),
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)
Thanks!
What I want is to use 2 columns for both minimum and maximum so not sure how will this solve the problem. Something like this -
CALENDAR (MINX ('02 Fact - MA', '02 Fact - MA'[ENR_DATE], '02 Fact - MA'[ADM_DATE]), MAXX (''02 Fact - MA', '02 Fact - MA'[ENR_DATE], '02 Fact - MA'[ADM_DATE]))