How do I create a calendar query

[Tome_05]

I am always grateful for your help.

Now, I would like to create a calendar query like the attached image in PowerBI, how can I create it? Create a calendar from April 22, 2022, with April 22nd to April 28th as week 1, April 29th to May 5th as week 2, and so on. I want to be able to automatically do the 3rd and 4th weeks every day.

[ryan_mayu · Accepted solution]

@Tome_05 

pls try this

月 = month('Table'[日付])&"月"

周 = "第"&rounddown(('Table'[日付]-min('Table'[日付]))/7,0)+1 & "周"

[amitchandak]

@Tome_05 , refer if my blogs can help

https://amitchandak.medium.com/cheat-sheet-power-bi-4-4-4-and-4-4-5-calendar-786f76da7d92

https://medium.com/chandakamit/cheat-sheet-calendar-that-starts-with-any-date-of-the-month-just-two-...

You need move FY Start date

var _cal =
ADDCOLUMNS( CALENDAR(DATE(2018,_FYENDMonth+1,01), _end)
, "Month Year" , FORMAT([Date], "MMM-YYYY")
, "Month year Sort" , Year([Date])*100 + month([date])
, "month start date" , EOMONTH([Date],-1)+1
, "month end date" , EOMONTH([Date],0)
, "FY year Start Date", if(month([Date])<=_FYENDMonth, EOMONTH([Date],-1*month([Date])- _FYRemain) ,EOMONTH([Date],-1*month([Date])+_FYENDMonth ) )+1
, "FY year End Date", if(month([Date])<=_FYENDMonth, EOMONTH([Date],_FYENDMonth-1*month([Date])) ,EOMONTH([Date],12-1*(month([Date])- _FYENDMonth) ))
,"Weekday name", format([date], "dddd")
, "Weekday", WEEKDAY([Date],2)
,"Start Week Date" , [Date] -1* WEEKDAY([Date],2) +1
,"End Week Date" , [Date] + 7 -1* WEEKDAY([Date],2)
, "Cal Year",year([date]))
Var _cal_cal2 =AddColumns( _cal,
"FY" , Year([FY year Start Date]),
"FY Month" , Datediff([FY year Start Date], [month start date],MONTH)+1 ,
"FY Qtr" , Quotient(Datediff([FY year Start Date], [month start date],MONTH),3)+1 ,
"FY Qtr Start" , Eomonth([FY year Start Date],Quotient(Datediff([FY year Start Date], [month start date],MONTH),3)*3-1)+1 ,
"FY Qtr End" , Eomonth([FY year Start Date],Quotient(Datediff([FY year Start Date], [month start date],MONTH),3)*3+2) ,
"FY Week Start",[FY year Start Date] -WEEKDAY([FY year Start Date],2)+1
)
//var _end = ENDOFYEAR(Sales[Sales Date])
return
ADDCOLUMNS(_cal_cal2,
"FY Year Month" , [FY]*100 + [FY Month]
,"FY Week" , QUOTIENT(DATEDIFF([FY Week Start],[Date],day),7)+1
,"FY Week No" , [FY]*100 + QUOTIENT(DATEDIFF([FY Week Start],[Date],day),7)+1

)