Forum Discussion

Pdawar's avatar
Pdawar
Frequent Visitor
3 years ago

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 ...
"Need Help"
@Show and Tell
Tips and Tricks
some_bih's avatar
some_bih
Icon for Community Champion rankCommunity Champion
3 years ago

Hi Pdawar  Please use code below to Create Date Table. adjust Sheet30 and Sheet1 to your Table names, also columns [DateTest] adjust to your columns in respective table and columns name. Basically, compared to previous version of DAX code, this solution check Min / Max dates. 

If this reply still couldn't help you solve your issue, please share a sample file with me.

Date =
--adjust Sheet30 and Sheet1 to your Table names
VAR MinYear_30 = YEAR ( MIN ( Sheet30[DateTest]) )
VAR MinYear_1 = YEAR ( MIN ( Sheet1[DateTest]) )
VAR Final_Min_Year= IF(MinYear_30 > MinYear_1,  MinYear_1, MinYear_30)

VAR MaxYear_30 = YEAR ( MAX ( Sheet30[DateTest]) )
VAR MaxYear_1 = YEAR ( MAX ( Sheet1[DateTest]) )
VAR Final_Max_Year= IF(MaxYear_30 > MaxYear_1,  MaxYear_30, MaxYear_1)

VAR MaxYear = YEAR (  TODAY() )
RETURN
ADDCOLUMNS (
    FILTER (
        CALENDARAUTO( ),
        AND ( YEAR ( [Date] ) >= Final_Min_Year, YEAR ( [Date] ) <= Final_Max_Year )
    ),
    "Calendar Year", "CY " & YEAR ( [Date] ),
    "Year", year([Date]),
    "Month Name", FORMAT ( [Date], "mmmm" ),
    "EOM and future",
        IF([Date]>=EOMONTH(TODAY(),-2)
            , TRUE,FALSE),
    "End of Month", EOMONTH([Date],0),
    "Month Number", MONTH ( [Date] )
)