Forum Discussion

rpinxt's avatar
rpinxt
Solution Sage
4 years ago
Solved

Calenderauto on specific date field

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?

  • amitchandak's avatar
    amitchandak
    4 years ago

    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]) ))

     

     

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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

  • rpinxt's avatar
    rpinxt
    Solution Sage
    4 years ago

    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.
    • amitchandak's avatar
      amitchandak
      Super User
      4 years ago

      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]) ))