Solved: 10 Days Range

MHTANK · ‎03-15-2025

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 · ‎03-15-2025

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
    )

✔
Any kudos or recognition appreciated. To learn more on the topic, check out my blog and follow me on LinkedIn.

View solution in original post

DataNinja777 · ‎03-15-2025

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,

View solution in original post

Ashish_Mathur · ‎03-15-2025

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

vojtechsima · ‎03-17-2025

@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

✔
Any kudos or recognition appreciated. To learn more on the topic, check out my blog and follow me on LinkedIn.

View solution in original post

MHTANK · ‎03-17-2025

Thanks to all 👍

Ashish_Mathur · ‎03-15-2025

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.

Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

MHTANK · ‎03-17-2025

Your solution is also true and shortly very well 👍

DataNinja777 · ‎03-15-2025

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 · ‎03-15-2025

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)

✔
Any kudos or recognition appreciated. To learn more on the topic, check out my blog and follow me on LinkedIn.

MHTANK · ‎03-17-2025

By this I am getting tables.

vojtechsima · ‎03-17-2025

@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

✔
Any kudos or recognition appreciated. To learn more on the topic, check out my blog and follow me on LinkedIn.

MHTANK · ‎03-17-2025

Yes This is also accepted 👍

danextian · ‎03-15-2025

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" )
    )

Dane Belarmino | Microsoft MVP | Proud to be a Super User!

Did I answer your question? Mark my post as a solution!

"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

vojtechsima · ‎03-15-2025

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
    )

✔
Any kudos or recognition appreciated. To learn more on the topic, check out my blog and follow me on LinkedIn.