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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. 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]) ))

 

 

 

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

 

 

 

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
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 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

June 2025 community update carousel

Fabric Community Update - June 2025

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