Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rpinxt
Impactful Individual
Impactful Individual

Calenderauto on specific date field

So I have a calenderauto via DAX:

rpinxt_0-1654251917285.png

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?

1 ACCEPTED SOLUTION

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

 

 

 

View solution in original post

3 REPLIES 3
rpinxt
Impactful Individual
Impactful Individual

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.

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

 

 

 

amitchandak
Super User
Super User

@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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.