Forum Discussion
10 Days Range
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 )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,
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 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
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)
By this I am getting tables.
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 createTextYes This is also accepted 👍
