10 Days Range

[MHTANK]

Date
01-01-2025
02-01-2025
04-01-2025
05-01-2025
07-01-2025
08-01-2025
09-01-2025
10-01-2025
15-01-2025
17-01-2025
20-01-2025
22-01-2025
30-01-2025
01-02-2025
02-02-2025
03-02-2025
05-02-2025
07-02-2025
08-02-2025
09-02-2025
10-02-2025
15-02-2025
18-02-2025
20-02-2025
22-02-2025
27-02-2025

This is my data where there is one column of Date.
I want below result:

Date	Day_Range
01-01-2025	1-10
02-01-2025	1-10
04-01-2025	1-10
05-01-2025	1-10
07-01-2025	1-10
08-01-2025	1-10
09-01-2025	1-10
10-01-2025	1-10
15-01-2025	11-20
17-01-2025	11-20
20-01-2025	11-20
22-01-2025	21-31
30-01-2025	21-31
01-02-2025	1-10
02-02-2025	1-10
03-02-2025	1-10
05-02-2025	1-10
07-02-2025	1-10
08-02-2025	1-10
09-02-2025	1-10
10-02-2025	1-10
15-02-2025	11-20
18-02-2025	11-20
20-02-2025	11-20
22-02-2025	21-28
27-02-2025	21-28

Here I want 10 days gap.

For example from date 1 to 10 I want 1-10, for 11 to 20==>11-20 and from 21 to 30 ==> 21-30.
But problem is that I want if there is month of 28 days then want 21-28 and if there is month of 31 days then I want 21-31.
So please suggest me how I can solve this?

[vojtechsima · Accepted solution]

Hey man, @danextian ,

you forgot about leap year:

You can do this instead:

Date Range = 
VAR _DayOfMonth =
    DAY ( 'Table'[Column1] )
VAR _DaysInMonth =
    DAY ( EOMONTH ( 'Table'[Column1], 0 ) )
RETURN
    SWITCH (
        TRUE (),
        _DayOfMonth <= 10, "1-10",
        _DayOfMonth <= 20, "11-20",
        "21-"&_DaysInMonth
    )

[DataNinja777 · Accepted solution]

Hi @MHTANK ,

You can create the Day_Range column in DAX using a calculated column formula. The formula first extracts the day from the date using DAY('Table'[Date]). Then, it determines the last day of the month using EOMONTH('Table'[Date], 0). The SWITCH(TRUE(), ...) function is used to assign the appropriate range: values between 1 and 10 are labeled "1-10," values between 11 and 20 are labeled "11-20," and values from 21 onward are dynamically assigned using "21-" & FORMAT(MonthEnd, "0"), ensuring that the last day of each month is correctly considered, whether it's 28, 30, or 31 days.

Day_Range = 
VAR DayNum = DAY('Table'[Date])
VAR MonthEnd = DAY( EOMONTH('Table'[Date], 0) ) 
RETURN
    SWITCH(
        TRUE(),
        DayNum <= 10, "1-10",
        DayNum <= 20, "11-20",
        "21-" & FORMAT(MonthEnd, "0")
    )

Replace 'Table' with the actual name of your table in Power BI. This formula ensures that the correct 10-day grouping is applied while dynamically adjusting for different month lengths.

Best regards,

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

Column = if(DAY(Data[Date])<=10,"1-10",if(day(Data[Date])<=20,"11-20","21-"&day(EOMONTH(Data[Date],0))))

Hope this helps.

[MHTANK]

Thanks to all 👍

[Ashish_Mathur · Accepted solution]

Hi,

Try this calculated column formula

Column = if(DAY(Data[Date])<=10,"1-10",if(day(Data[Date])<=20,"11-20","21-"&day(EOMONTH(Data[Date],0))))

Hope this helps.

[MHTANK]

Your solution is also true and shortly very well 👍

[DataNinja777 · Accepted solution]

Hi @MHTANK ,

You can create the Day_Range column in DAX using a calculated column formula. The formula first extracts the day from the date using DAY('Table'[Date]). Then, it determines the last day of the month using EOMONTH('Table'[Date], 0). The SWITCH(TRUE(), ...) function is used to assign the appropriate range: values between 1 and 10 are labeled "1-10," values between 11 and 20 are labeled "11-20," and values from 21 onward are dynamically assigned using "21-" & FORMAT(MonthEnd, "0"), ensuring that the last day of each month is correctly considered, whether it's 28, 30, or 31 days.

Day_Range = 
VAR DayNum = DAY('Table'[Date])
VAR MonthEnd = DAY( EOMONTH('Table'[Date], 0) ) 
RETURN
    SWITCH(
        TRUE(),
        DayNum <= 10, "1-10",
        DayNum <= 20, "11-20",
        "21-" & FORMAT(MonthEnd, "0")
    )

Replace 'Table' with the actual name of your table in Power BI. This formula ensures that the correct 10-day grouping is applied while dynamically adjusting for different month lengths.

Best regards,

[vojtechsima]

Hey, @MHTANK ,

In Power Query / M language, you can do this:

static but clean:

    addRange2= Table.AddColumn(addRange, "range2", each
    let
    daysInMonth = Date.DaysInMonth([Date]),
    currentDay = Date.Day([Date]),
    createText = 
    if currentDay <= 10 then "1-10" 
    else if currentDay <= 20 then "11-20" 
    else "21-"&Text.From(daysInMonth)
    in
    createText, type text)

or more dynamic 

    // replace changeType to your PreviousStep
    addRange= Table.AddColumn(changeType, "range", each
    let
    daysInMonth = Date.DaysInMonth([Date]),
    currentDay = Date.Day([Date]),
    split = List.Split({1..daysInMonth}, 10),
    removeLast = List.Select(split, each not List.Contains(_, 31)),
    if31AddBack = if daysInMonth = 31 then List.Transform( removeLast, each if List.Contains(_, 30) then _ & {31} else _) else removeLast,
    pickList = List.Select(if31AddBack, each List.Contains(_, currentDay)),
    combineIt = List.Combine(pickList),
    createText = Text.From(List.First(combineIt)) & "-" & Text.From(List.Last(combineIt))
    in
    createText, type text)

[MHTANK]

By this I am getting tables.

[vojtechsima · Accepted solution]

@MHTANK  I am not exactly sure what you mean, but you can do  in Power Querry:
Add Column and then copy just this part:

let
    daysInMonth = Date.DaysInMonth([Date]),
    currentDay = Date.Day([Date]),
    createText = 
    if currentDay <= 10 then "1-10" 
    else if currentDay <= 20 then "11-20" 
    else "21-"&Text.From(daysInMonth)
in
    createText

[MHTANK]

Yes This is also accepted 👍

[danextian]

Hi @MHTANK 

Please try this calculated column:

Date Range = 
VAR _DayOfMonth =
    DAY ( Data[Date] )
VAR _DaysInMonth =
    DAY ( EOMONTH ( Data[Date], 0 ) )
RETURN
    SWITCH (
        TRUE (),
        _DayOfMonth <= 10, "1-10",
        _DayOfMonth <= 20, "11-20",
        IF ( _DaysInMonth >= 29, "21-31", "21-28" )
    )

[vojtechsima · Accepted solution]

Hey man, @danextian ,

you forgot about leap year:

You can do this instead:

Date Range = 
VAR _DayOfMonth =
    DAY ( 'Table'[Column1] )
VAR _DaysInMonth =
    DAY ( EOMONTH ( 'Table'[Column1], 0 ) )
RETURN
    SWITCH (
        TRUE (),
        _DayOfMonth <= 10, "1-10",
        _DayOfMonth <= 20, "11-20",
        "21-"&_DaysInMonth
    )