Calendar function dependent on 2 date columns

Question

Hi,&nbsp;&nbsp;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)&nbsp; from the fact table. I am currently using -&nbsp;CALENDAR (MINX ('02 Fact - MA', '02 Fact - MA'[ENR_DATE]), MAXX ('02 Fact - MA', '02 Fact - MA'[ENR_DATE]))&nbsp;but my range should be dependent on 2 columns. Both columns are present in the same fact table. I want to take MIN from ENR_DATE and ADM_DATE and similarly max from&nbsp;ENR_DATE and ADM_DATE. Is that possible to do?&nbsp;Thanks!

some_bih · Answer

Hi&nbsp;Pdawar&nbsp;from original reference link&nbsp;https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/&nbsp;Adjust code part&nbsp; Sales[Order Date] to your columns . Hope this help&nbsp;Date&nbsp;=VAR&nbsp;MinYear&nbsp;=&nbsp;YEAR&nbsp;(&nbsp;MIN&nbsp;(&nbsp;Sales[Order Date]&nbsp;)&nbsp;)VAR&nbsp;MaxYear&nbsp;=&nbsp;YEAR&nbsp;(&nbsp;MAX&nbsp;(&nbsp;Sales[Order Date]&nbsp;)&nbsp;)RETURNADDCOLUMNS&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;FILTER&nbsp;(&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CALENDARAUTO(&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;AND&nbsp;(&nbsp;YEAR&nbsp;(&nbsp;[Date]&nbsp;)&nbsp;&gt;=&nbsp;MinYear,&nbsp;YEAR&nbsp;(&nbsp;[Date]&nbsp;)&nbsp;&lt;=&nbsp;MaxYear&nbsp;)&nbsp;&nbsp;&nbsp;&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"Calendar Year",&nbsp;"CY "&nbsp;&amp;&nbsp;YEAR&nbsp;(&nbsp;[Date]&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"Month Name",&nbsp;FORMAT&nbsp;(&nbsp;[Date],&nbsp;"mmmm"&nbsp;),&nbsp;&nbsp;&nbsp;&nbsp;"Month Number",&nbsp;MONTH&nbsp;(&nbsp;[Date]&nbsp;))

pdawar · Answer

Thanks!&nbsp;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 -&nbsp;CALENDAR&nbsp;(MINX&nbsp;('02 Fact - MA',&nbsp;'02 Fact - MA'[ENR_DATE],&nbsp;'02 Fact - MA'[ADM_DATE]),&nbsp;MAXX&nbsp;(''02 Fact - MA',&nbsp;'02 Fact - MA'[ENR_DATE],&nbsp;'02 Fact - MA'[ADM_DATE]))&nbsp;&nbsp;

some_bih · Answer

Hi&nbsp;Pdawar&nbsp; Please use code below to Create Date Table.&nbsp;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.&nbsp;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 namesVAR MinYear_30 = YEAR ( MIN ( Sheet30[DateTest]) )VAR MinYear_1 = YEAR ( MIN ( Sheet1[DateTest]) )VAR Final_Min_Year= IF(MinYear_30 &gt; MinYear_1, &nbsp;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 &gt; MaxYear_1, &nbsp;MaxYear_30, MaxYear_1)VAR MaxYear = YEAR ( &nbsp;TODAY() ) RETURNADDCOLUMNS (&nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; CALENDARAUTO( ),&nbsp; &nbsp; &nbsp; &nbsp; AND ( YEAR ( [Date] ) &gt;= Final_Min_Year, YEAR ( [Date] ) &lt;= Final_Max_Year )&nbsp; &nbsp; ),&nbsp; &nbsp; "Calendar Year", "CY " &amp; YEAR ( [Date] ),&nbsp; &nbsp; "Year", year([Date]),&nbsp; &nbsp; "Month Name", FORMAT ( [Date], "mmmm" ),&nbsp; &nbsp; "EOM and future", &nbsp; &nbsp; &nbsp; &nbsp; IF([Date]&gt;=EOMONTH(TODAY(),-2)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; , TRUE,FALSE),&nbsp; &nbsp; "End of Month", EOMONTH([Date],0),&nbsp; &nbsp; "Month Number", MONTH ( [Date] ))

Forum Discussion

Calendar function dependent on 2 date columns

3 Replies

Related content

Function Calendar Wrong WeekNum

Circular dependency

Dependent filter function

using the Calendar function and power query

Calendar Slicer with Different Business Calendars