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
JustinDoh1
Post Prodigy
Post Prodigy

Create a calendar table that only shows only one day out of a week

I am trying to create a calendar that shows only Sunday (one day out of a week).

 

This is code that I used to create a Calendar table.

 

Simple Date =
var DateStart = DATE(2021 , 7 , 1)
var DateEnd = DATE(2021 , 12 , 31)
return
ADDCOLUMNS(
CALENDAR(DateStart , DateEnd)
, "weeknum", WEEKNUM(''[Date] , 1) //week begins on Sunday
)

 

JustinDoh1_0-1632961267589.png

Expected output is showing only rows that have

7/4/21

7/11/21

7/18/21

...

 

How do I modify on this DAX?

 

Thanks.

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Picture2.png

 

Simple Date =
VAR DateStart =
DATE ( 2021, 7, 1 )
VAR DateEnd =
DATE ( 2021, 12, 31 )
VAR simpledatetablesonlysunday =
FILTER (
ADDCOLUMNS ( CALENDAR ( DateStart, DateEnd ), "weeknum", WEEKNUM ( [Date], 1 ) ),
WEEKDAY ( [Date] ) = 1
)
RETURN
simpledatetablesonlysunday

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

2 REPLIES 2
Jihwan_Kim
Super User
Super User

Picture2.png

 

Simple Date =
VAR DateStart =
DATE ( 2021, 7, 1 )
VAR DateEnd =
DATE ( 2021, 12, 31 )
VAR simpledatetablesonlysunday =
FILTER (
ADDCOLUMNS ( CALENDAR ( DateStart, DateEnd ), "weeknum", WEEKNUM ( [Date], 1 ) ),
WEEKDAY ( [Date] ) = 1
)
RETURN
simpledatetablesonlysunday

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


VahidDM
Super User
Super User

Hi @JustinDoh1 

 

Try this code to create your table:

Simple Date =
VAR DateStart =
    DATE ( 2021, 7, 1 )
VAR DateEnd =
    DATE ( 2021, 12, 31 )
VAR DateTable =
    ADDCOLUMNS (
        CALENDAR ( DateStart, DateEnd ),
        "weeknum", WEEKNUM ( ''[Date], 1 ),
        "Day Name", FORMAT ( [Date], "dddd" )
    )
RETURN
    FILTER ( DateTable, [Day Name] = "Sunday" )

Output:

VahidDM_0-1632969344685.png

 

 

You can cretae your Calendar table in Power Query as well, see this link:

https://www.vahiddm.com/post/creating-calendar-table-with-3-steps

 

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

Appreciate your Kudos✌️!!

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