Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during AI Skills Fest. This week only. Secure your voucher now.

Reply
MHTANK
Helper III
Helper III

10 Days Range

‎03-15-2025 01:08 AM
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:

DateDay_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?

Labels:
Message 1 of 11
1,156 Views
0
4 ACCEPTED SOLUTIONS
vojtechsima
Super User
Super User
In response to danextian

‎03-15-2025 03:34 AM

Hey man, @danextian ,

you forgot about leap year:

vojtechsima_0-1742034808053.png

You can do this instead:

vojtechsima_1-1742034836509.png

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

Message 3 of 11
1,129 Views
DataNinja777
Super User
Super User

‎03-15-2025 04:16 AM

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

Message 8 of 11
1,117 Views
Ashish_Mathur
Super User
Super User

‎03-15-2025 08:38 PM

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.

Ashish_Mathur_0-1742096315097.png

 


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

View solution in original post

Message 9 of 11
1,084 Views
vojtechsima
Super User
Super User
In response to MHTANK

‎03-17-2025 01:54 AM

@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

Message 6 of 11
1,048 Views
10 REPLIES 10
MHTANK
Helper III
Helper III

‎03-17-2025 02:02 AM

Thanks to all 👍

Message 11 of 11
1,040 Views
0
Ashish_Mathur
Super User
Super User

‎03-15-2025 08:38 PM

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.

Ashish_Mathur_0-1742096315097.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Message 9 of 11
1,085 Views
MHTANK
Helper III
Helper III
In response to Ashish_Mathur

‎03-17-2025 01:54 AM

Your solution is also true and shortly very well 👍

Message 10 of 11
1,047 Views
0
DataNinja777
Super User
Super User

‎03-15-2025 04:16 AM

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,

Message 8 of 11
1,118 Views
vojtechsima
Super User
Super User

‎03-15-2025 03:24 AM

Hey, @MHTANK ,

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

vojtechsima_0-1742034239534.png

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.
Message 4 of 11
1,132 Views
MHTANK
Helper III
Helper III
In response to vojtechsima

‎03-17-2025 01:46 AM

By this I am getting tables.

Message 5 of 11
1,056 Views
0
vojtechsima
Super User
Super User
In response to MHTANK

‎03-17-2025 01:54 AM

@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.
Message 6 of 11
1,049 Views
MHTANK
Helper III
Helper III
In response to vojtechsima

‎03-17-2025 01:59 AM

Yes This is also accepted 👍

Message 7 of 11
1,041 Views
0
danextian
Super User
Super User

‎03-15-2025 03:23 AM

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

danextian_0-1742034181558.png

 





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.
Message 2 of 11
1,133 Views
vojtechsima
Super User
Super User
In response to danextian

‎03-15-2025 03:34 AM

Hey man, @danextian ,

you forgot about leap year:

vojtechsima_0-1742034808053.png

You can do this instead:

vojtechsima_1-1742034836509.png

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.
Message 3 of 11
1,130 Views

Helpful resources

Announcements
May Power BI Update Carousel

Power BI Monthly Update - May 2026

Check out the May 2026 Power BI update to learn about new features.

Fabric SQL PBI Data Days

Data Days 2026 coming soon!

Sign up to receive a private message when registration opens and key events begin.

New to Fabric survey Carousel

New to Fabric Survey

If you have recently started exploring Fabric, we'd love to hear how it's going. Your feedback can help with product improvements.

Power BI DataViz World Championships carousel

Power BI DataViz World Championships - June 2026

A new Power BI DataViz World Championship is coming this June! Don't miss out on submitting your entry.

View All