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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
rpinxt
Power Participant
Power Participant

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

 

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

3 REPLIES 3
rpinxt
Power Participant
Power Participant

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

 

 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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