Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
priyamsachan12
Frequent Visitor

Calendar with Wkly calculation where W1 should have more than 4 days and last week more than 2 days

‎03-04-2024 08:58 AM

I need the calendar table to be created in Power BI as in the image. It has to be dynamic for each year.
Can anyone help me with this?

 

priyamsachan12_0-1709571362337.png

 

Labels:
Message 1 of 4
369 Views
0
3 REPLIES 3
priyamsachan12
Frequent Visitor

‎03-13-2024 04:51 AM

@v-kaiyue-msft 

Can you help in the above calendar?

Message 4 of 4
255 Views
0
v-kaiyue-msft
Community Support
Community Support

‎03-05-2024 01:52 AM

Hi @priyamsachan12 ,

1. According to your description, I created the date table.

Table =
Calendar (
Date (2024,1,1),
Date (2024,4,30)))


2. Create the calculation column.

Weeknum =
Weeknum ('table' [date], 2)

3. Create the calculation column.

Weekday =
Weekday ('Table' [Date], 2)

4. Create the calculation column to get the order of this month.

Week =
RANKX (RANKX
Filter (all ('table'),
Year ('table' [date]) = year (earlier ('table' [date])) && Month ('table' [date]) = month (earlier ('table' [date])), [weeknum], , ASC, DENSE)

5.Create the calculation column.

Column = 
var _count=
COUNTX(
    FILTER(ALL('Table'),
    YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[Week]=1),[Date])
return
IF(
    _count<4&&'Table'[Week]=1,_count +7,
    IF(
        _count>=4&&'Table'[Week]=1,_count,_count))

6. Create the calculation column to get the date of different weeks.

Test = 
var _mindate=
MINX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[Week]=EARLIER('Table'[Week])),
    [Date])
var _maxdate=
MAXX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[Week]=EARLIER('Table'[Week])),
    [Date])
var _maxweek=
MAXX(FILTER(ALL('Table'),YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))),[Week])
var _count=COUNTX(
    FILTER(ALL('Table'),
    YEAR('Table'[Date])=YEAR(EARLIER('Table'[Date]))&&MONTH('Table'[Date])=MONTH(EARLIER('Table'[Date]))&&'Table'[Week]=1),[Date])
var _value=_mindate+6
var _if=
IF(
    _value>_maxdate,_maxdate,_value)
return
SWITCH(
    TRUE(),
    [Week]=_maxweek&&[Column]<4,"blank",
    [Column]>7,_mindate&"-"&_mindate+[Column]-1,
    [Column]=7,_mindate&"-"&_if,
    [Column]>3&&[Column]<7,_mindate&"-"&_if,
    [Column]<=3,_mindate+7&"-"&_mindate+7+6
       
)

 

If your Current Period does not refer to this, please clarify in a follow-up reply.

 

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Calendar with Wkly calculation where W1 should have more than 4 days and last week more than 2 days.pbix
Message 2 of 4
308 Views
0
priyamsachan12
Frequent Visitor
In response to v-kaiyue-msft

‎03-07-2024 09:55 PM

Thanks for the response.
I want the calendar table from above image not exactly the columns as image.

Logic: Week1 Should have more than 4 days else it will end on next sunday and last week should have min. 3 days else it will be merged with its previous week.

Reference Table: 

priyamsachan12_1-1709877271108.png


Below is the output table expected in Power BI.

YearMonthWeekStartDateWeekEndDateWeekNumber
2024January01-01-202407-01-20241
2024January08-01-202414-01-20242
2024January15-01-202421-01-20243
2024January22-01-202428-01-20244
2024January29-01-202431-01-20245
2024February01-02-202411-02-20241
2024February12-02-202418-02-20242
2024February19-02-202425-02-20243
2024February26-02-202429-02-20244
2024March01-03-202410-03-20241
2024March11-03-202417-03-20242
2024March18-03-202424-03-20243
2024March25-03-202431-03-20244
2024April01-04-202407-04-20241
2024April08-04-202414-04-20242
2024April15-04-202421-04-20243
2024April22-04-202430-04-20244
2024May01-05-202405-05-20241
2024May06-05-202412-05-20242
2024May13-05-202419-05-20243
2024May20-05-202426-05-20244
2024May27-05-202431-05-20245
2024June01-06-202409-06-20241
2024June10-06-202416-06-20242
2024June17-06-202423-06-20243
2024June24-06-202430-06-20244
2024July01-07-202407-07-20241
2024July08-07-202414-07-20242
2024July15-07-202421-07-20243
2024July22-07-202428-07-20244
2024July29-07-202431-07-20245
2024August01-08-202411-08-20241
2024August12-08-202418-08-20242
2024August19-08-202425-08-20243
2024August26-08-202431-08-20244
2024September01-09-202411-09-20241
2024September09-09-202415-09-20242
2024September16-09-202422-09-20243
2024September23-09-202430-09-20244
2024October01-10-202406-10-20241
2024October07-10-202413-10-20242
2024October14-10-202420-10-20243
2024October21-10-202427-10-20244
2024October28-10-202431-10-20245
2024November01-11-202410-11-20241
2024November11-11-202417-11-20242
2024November18-11-202424-11-20243
2024November25-11-202430-11-20244
2024December01-12-202408-12-20241
2024December09-12-202415-12-20242
2024December16-12-202422-12-20243
2024December23-12-202431-12-20244

 

 

 




Message 3 of 4
289 Views
0

Helpful resources

Announcements
ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

View All