Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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?)
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
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?)
User | Count |
---|---|
42 | |
28 | |
24 | |
20 | |
16 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |