Forum Discussion

Tome_05's avatar
Tome_05
Helper III
4 years ago
Solved

How do I create a calendar query

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.

 

Need Help
Tips and Tricks
  • ryan_mayu's avatar
    ryan_mayu
    4 years ago

    Tome_05 

    pls try this

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

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

2 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    4 years ago

    Tome_05 

    pls try this

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

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

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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-variables-apart-9fb9a0f65b92

     

    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

    )