Change start date and End date of month

[jckypatel]

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

[BA_Pete · Accepted 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

[jckypatel]

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.  

[rubayatyasmin]

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.