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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BharathM
Helper IV
Helper IV

Need all mondays in one date column and all sundays in another column

Hi Everyone

Hope Everyone doing good 

 

I need to create one table that table should have only two columns

 

Column 1 and column 2 

 

In the column 1 dates of Monday i need 

In column 2 dates of sunday i need 

 

I don't want  any other columns in that table

 

For example Dates of column 1

1/3/2022,

1/10/2022

1/17/2022 etc.. only monday i want see

 

For Example column 2 are 

1/9/2022

1/16/2022

1/23/2022 etc ... Only Sunday's i want see

 

Thanks in advance please note i want to see only this two columns in the table

 

 

 

1 ACCEPTED SOLUTION

Hello Bharath,

 

This is what I wrote to get a calculated table of rows of just Monday to Sunday.

Calendar = 
ADDCOLUMNS ( -- ADDCOLUMNS to add the second, "Week End" column
    SELECTCOLUMNS ( -- SELECTCOLUMNS function to rename the column to Week Start; remove this function if you don't care to rename the default "Date" column from CALENDAR
        FILTER (
            CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2023, 12, 31 ) ), -- sample calendar, adjust as needed
            WEEKDAY ( [Date], 2 ) = 1
        ),
        "Week Start", [Date]
    ),
    "Week End", [Week Start] + 6
)

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

View solution in original post

6 REPLIES 6
Wilson_
Solution Sage
Solution Sage

Hello Bharath,

 

This is doable. However, why do you want this? What is this helping you achieve? I'm making an assumption here, but this sounds like the first step in a clunky method to actually do something else.

Hi @Wilson_ 

I want to use it as calendar table actually i need to show week(week range ) column in slicer and other tables have week number i want to use it modeling part i will upload samples data image here

BharathM_0-1681480370053.png

 i am going to create key in this table concatenation with week number and year in other tables have week number and year i will make realtionship with this key and client want to see the week(week range) column in the slicer and also I don't want any other columns in this table

 

Thanks for reply 

 

 

Hello Bharath,

 

This is what I wrote to get a calculated table of rows of just Monday to Sunday.

Calendar = 
ADDCOLUMNS ( -- ADDCOLUMNS to add the second, "Week End" column
    SELECTCOLUMNS ( -- SELECTCOLUMNS function to rename the column to Week Start; remove this function if you don't care to rename the default "Date" column from CALENDAR
        FILTER (
            CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2023, 12, 31 ) ), -- sample calendar, adjust as needed
            WEEKDAY ( [Date], 2 ) = 1
        ),
        "Week Start", [Date]
    ),
    "Week End", [Week Start] + 6
)

----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)

Hi @Wilson_ Its working as expected thank you so much

hi @BharathM 

Why not do it in Excel and import it in Power BI?

Hi @FreemanZ 

Actually my client need this way they don't want any Excel 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors