Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
My start date of every month is 25th and end date is 24th.
For example - For September 2023, I need date from 25th August 2023 To 24th September 2023
Plz help me
Solved! Go to Solution.
Hi @jckypatel ,
I'm assuming you're starting from a list of contiguous dates in a calendar table. As such, try this:
let
Source =
List.Transform(
{ Number.From(#date(2023,11,1))..Number.From(#date(2024,03,31)) },
each Date.From(_)
),
convToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
chgDateType = Table.TransformColumnTypes(convToTable, {{"Column1", type date}}),
renCols = Table.RenameColumns(chgDateType, {{"Column1", "date"}}),
// Relevant step here ----->
addMonthYear =
Table.AddColumn(
renCols,
"monthYear",
each if Date.Day([date]) >= 25
then Text.Combine({Date.MonthName(Date.AddMonths([date], 1)), Text.From(Date.Year(Date.AddMonths([date], 1)))}, " ")
else Text.Combine({Date.MonthName([date]), Text.From(Date.Year([date]))}, " ")
)
in
addMonthYear
It's only the 'addMonthYear' step you need, the rest of it is just building the date list, which you should already have.
Example output:
Pete
Proud to be a Datanaut!
Hi @jckypatel ,
I'm assuming you're starting from a list of contiguous dates in a calendar table. As such, try this:
let
Source =
List.Transform(
{ Number.From(#date(2023,11,1))..Number.From(#date(2024,03,31)) },
each Date.From(_)
),
convToTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
chgDateType = Table.TransformColumnTypes(convToTable, {{"Column1", type date}}),
renCols = Table.RenameColumns(chgDateType, {{"Column1", "date"}}),
// Relevant step here ----->
addMonthYear =
Table.AddColumn(
renCols,
"monthYear",
each if Date.Day([date]) >= 25
then Text.Combine({Date.MonthName(Date.AddMonths([date], 1)), Text.From(Date.Year(Date.AddMonths([date], 1)))}, " ")
else Text.Combine({Date.MonthName([date]), Text.From(Date.Year([date]))}, " ")
)
in
addMonthYear
It's only the 'addMonthYear' step you need, the rest of it is just building the date list, which you should already have.
Example output:
Pete
Proud to be a Datanaut!
Thanx for the help....
Also i am adding in this.... I have to convert this Month in to Financial Year & financial Quarter. E.G April 2023 to March 2024 is my 2023-24 Financial Year & April to June is My Quarter 1.
Kindly help me.
Hi, @jckypatel
Create a custom date table using DAX. For e.g:
CustomDateTable =
ADDCOLUMNS (
CALENDAR (DATE(Year(TODAY())-1, MONTH(TODAY())-1, 25), DATE(Year(TODAY()), MONTH(TODAY()), 24)),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Month Name", FORMAT([Date], "MMMM"),
"Year-Month", FORMAT([Date], "YYYY-MMM")
)
Mark the date column as Date then create relationship with your desired table.
Proud to be a Super User!