Number of Sundays in between dates

[PrakashPalle]

Hi Power BI Experts,

How to find number of sundays existed in between start & end dates.

Please share the dax code how to calculate number of sundays in output section. Thanks in advance.

[foodd · Accepted solution]

Hello @PrakashPalle , the following DAX calculates the number of Sundays row by row when a Start Date and End date is supplied.

Number of Sundays = 
VAR NumOfWeeks = INT(DATEDIFF([StartDate], [EndDate], WEEK))
VAR NumOfSundays =
    COUNTROWS(
        FILTER(
            GENERATESERIES([StartDate], [EndDate], 1),
            WEEKDAY([Value]) = 1
        )
    )
RETURN
    IF(NumOfWeeks = 0, IF(WEEKDAY([StartDate]) = 1, 1, 0), NumOfSundays)

=-=-=-=-=-

If you find this insightful, please provide a Kudo and Accept this as a Solution so that others may find a Solution easier when searching.

[PrakashPalle]

@foodd thanks for your reply. I tried to replicate the code i am getting below error. Please help.

[foodd]

Please upload your Excel source file, and your 

work in progress Power BI Desktop file.

[PrakashPalle]

Thanks @foodd , for the reply.

I have added startdate & endate blanks in your data. I am getting the same error. 

[foodd]

Please download, open, and refresh the PowerBI Desktop File attached to the reply with the Power Query M-Code.    There are no errors produced, and the data table was Entered as data in the PBIX itself.   Your original post does not reference Blank Rows, and as a result, the working M-Code produces no errors.   Please do reply with constructive feedback should you find that the Power BI Desktop file fails to load and display the desired results upon opening.

[dulan_kavinda]

Can you try with below DAX with the date column,

Is_Sunday =
IF(
WEEKDAY('TableName'[Date], 2) = 7,
1,
0
)

If you have 2 separate column for start date and end date in the table please let me know.

[bhanu_gautam]

@PrakashPalle , You can use the formula mentioned below to calculate Number of Sundays

Number of Sundays =
VAR StartDate = MIN('Table'[Start Date])
VAR EndDate = MAX('Table'[End Date])
VAR NumOfWeeks = INT(DATEDIFF(StartDate, EndDate, WEEK))
VAR NumOfSundays =
COUNTX(
GENERATESERIES(StartDate, EndDate, 1),
IF(WEEKDAY([Value]) = 1, 1, 0)
)
RETURN
IF(NumOfWeeks = 0, IF(WEEKDAY(StartDate) = 1, 1, 0), NumOfSundays)

[PrakashPalle]

Thanks @bhanu_gautam for the reply.

I replicated the dax which you have shared, but unfortunately the expression is not giving the correct output. Below screen shot for your reference.