datepowerbidaxdate-range

Power BI Days in a given week of a month


I have the following columns - Week of Month, Month, Year, Date, & Days in Week. I am trying to show in the column "Days in Week" in this format: 1 - 4, 5 - 11, etc. In other words, I want the range of the days in that week to go into the column "Days in Week". Refer to the picture below where I show the results that I am looking for (don't pay any attention to the different colors):

enter image description here

The code that I am using only results in 1 - 31 which are the range of days in that month which looks like this:

Days in Week = IF('Date'[Year] = 'Date'[Year] && 'Date'[Month] = 'Date'[Month] && 'Date'[Adjusted Week Of Month] = 'Date'[Adjusted Week Of Month], MIN('Date'[Day]) & " - " & MAX('Date'[Day]), "")

The logic that I am going for is if the week, month, & year match then show the "min day in that week" "-" "max day in that week" as shown in the picture above.

Any and all help is appreciated!


Solution

  • If you want to calculate the result according to the method you described you can use the following dax-formula:

    Days in Week = CALCULATE( DAY(MIN('Table'[Date])), ALLEXCEPT('Table', 'Table'[Year], 'Table'[Month], 'Table'[Week of Month]) ) & " - " & CALCULATE( DAY(MAX('Table'[Date])), ALLEXCEPT('Table', 'Table'[Year], 'Table'[Month], 'Table'[Week of Month]) )
    

    This will result in the following table:

    enter image description here