topic Re: Calenderauto on specific date field in DAX Commands and Tips

Calenderauto on specific date field

rpinxt — Fri, 03 Jun 2022 10:30:27 GMT

So I have a calenderauto via DAX:

Now my data has 3 date fields.

Fields like Manufacturing Date and Use before date are messing up my min and max date that I want to have.

I want the calenderauto to only take dates from the reporting date field.

Is this possible?

Re: Calenderauto on specific date field

amitchandak — Fri, 03 Jun 2022 10:48:30 GMT

rpinxt , I tried it worked for me

Date = var _weekday =3 var _left = _weekday -7 var _tab = ADDCOLUMNS(CALENDARAUTO(),"Month Year",format([Date],"MMM-YYYY"), "Month Year Sort", FORMAT([Date], "YYYYMM"), "Year", YEAR([Date]) ,"QTR Year" , FORMAT([Date],"YYYY-\QQ"), "Week Year", YEAR([Date]) *100 + WEEKNUM([date],2), "Day", Today() - Weekday(Today(),1), "Weekday" , WEEKDAY([date]), "Week Start Date1" , [Date] + -1*WEEKDAY([Date])+1, "Week End Date1" , [Date] + -1*WEEKDAY([Date])+7, "Week Start Date" , [Date] + -1*if(WEEKDAY([Date])<=_weekday,WEEKDAY([Date],1)+_weekday,WEEKDAY([Date],1)+ _left) ) return ADDCOLUMNS(_tab, "Week Rank" , Rankx(_tab,[Week Year], ,ASC,Dense))

What is the issue

Re: Calenderauto on specific date field

rpinxt — Fri, 03 Jun 2022 12:29:01 GMT

Thanks amitchandak , but yes this code works.

But as said there are 3 date fields in my data.

So this code finds the first date and the last date of all dates in these 3 fields.

I wanted it to take only the dates of 1 specific date field and autocalendar on that field only and not the other 2.

But I now solved it likes this without autocalendar:

Dim_Date =

ADDCOLUMNS(

CALENDAR([MinRepDate],[MaxRepDate]),

"Year", YEAR([Date]),

"Quarter", FORMAT([Date],"\QQ"),

"Quarter Nr", QUARTER([Date]),

"Month", FORMAT([Date], "mmmm" ),

"Mth", FORMAT([Date], "mmm" ),

"Month Number", MONTH([Date]),

"Week Number", WEEKNUM([Date]),

"Week Day", FORMAT([Date], "DDD"),

"Week Day Nr", WEEKDAY([Date], 2),

"Period", FORMAT([Date], "YYYY-MM"),

"Period2", FORMAT([Date], "YYYY-MM")

)

So I made a Min and Max date on that 1 date field it al want it on.

Guess this is the best way.

Re: Calenderauto on specific date field

amitchandak — Fri, 03 Jun 2022 15:25:52 GMT

rpinxt , Then you can use of the calendar is best. auto-calendar has a different purpose

I usually force those date to go start and end of the year

CALENDAR(eomonth([MinRepDate], -1* Month([MinRepDate])) +1 ,eomonth([MaxRepDate] , 12 -1* Month([MinRepDate]) ))