powerbicalendardaxpowerqueryweekday

DAX Help : Filter on Full Weeks Only

I have an odd request to report data in PowerBI on days in the previous month that are part of a full Sunday-Saturday week.

For example, February 2023 would only show data from the 5th to the 25th: (https://i.sstatic.net/qldSK.png)

Days falling outside of those complete week cycles are to be omitted completely.

I have a datedim table with all the usuals, year/month/weeknum/weekbegindate/weekenddate/etc., but not sure how to go about putting this all together in PowerBI.

The data will be used in a matrix to show the average of instances occurring per day of the week for the previous month (example: total instances occurring on Sunday divided by the number of full Sun-Sat weeks in that month): (https://i.sstatic.net/xGeki.png)

Any help or direction would be greatly appreciated, I am stuck, stuck. Thanks!

What I've tried:

DaysOfWeek = COUNTX ( FILTER ( 'Date'; EARLIER ( 'Date'[YearWeek] ) = 'Date'[YearWeek] ); 'Date'[YearWeek] )

Afterwards I created my max-week logic (max week where DaysOfWeek = 7), however this method includes days rolling over from the previous month- which I want to exclude.

Solution

  • In PQ, do the following.

    1. Add Week of Month
    2. Add month

    enter image description here

    1. Group as follows:

    enter image description here

    1. Remove redundant columns, expand the table again and add a new column as follows

    enter image description here

    1. You end up with a table as follows.

    enter image description here

    Use the Custom column as part of your filter in DAX whenever you need to see full weeks only

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}}),
    #"Inserted Week of Month" = Table.AddColumn(#"Changed Type", "Week of Month", each Date.WeekOfMonth([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Week of Month", "Month", each Date.Month([Date]), Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Month", {"Month", "Week of Month"}, {{"Count", each Table.RowCount(_), Int64.Type}, {"All", each _, type table [Date=nullable date, Week of Month=number, Month=number]}}),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Month", "Week of Month"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Date"}, {"Date"}),
    #"Added Custom" = Table.AddColumn(#"Expanded All", "Custom", each if [Count] = 7 then "full" else "partial")
in
    #"Added Custom"