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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
rpinxt
Solution Sage
Solution Sage

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

 

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
rpinxt
Solution Sage
Solution Sage

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

 

 

 

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

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

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.