Forum Discussion

cedmiston's avatar
cedmiston
Frequent Visitor
6 years ago
Solved

Custom Month Creation

Hey all,   I'm trying to do calculations for a time report but hitting a snag because we report time from the 25th of each month to the 24th of the next, rather than on the calendar months. So for ...
Need Help
Tips and Tricks
  • ChrisMendoza's avatar
    ChrisMendoza
    6 years ago

    cedmiston - You could create additional columns as:

     

    NewMonthNum = 
IF (
    DAY ( 'Table'[Date] ) < 25,
    MONTH ( 'Table'[Date] ),
    MOD ( MONTH ( 'Table'[Date] ) + 1, 12 )
)

     

    and

     

    SwitchMonthName = 
SWITCH(
    TRUE(),
    'Table'[NewMonthNum] = 1, "Jan",
    'Table'[NewMonthNum] = 2, "Feb",
    "etc"
)

     

     Then you could use this as your slicer.

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    cedmiston

    Try create a month column, should be something like this:

     

    Month.NO = 
VAR Monthno_ =SWITCH (TRUE(),
    'Table'[Date].[Day]<25, MONTH([Date])-1,
    'Table'[Date].[Day]>=25,MONTH('Table'[Date]))
RETURN IF(Monthno_=0, MONTH(CALCULATE(MAX([Date]),FILTER('Table',[Date].[Year]=EARLIER('Table'[Date].[Year])-1))),Monthno_)

     

    Paul Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbeneyze's avatar
lbeneyze
Advocate II
6 years ago

Good morning cedmiston,

 

Hereby my Power query solution:

   - I've created a custom function "SetCustomMonth" that retunrs the month number based on another start day (25 instead of 1 in your example) 

   - you can invoke this function in Power Query to create a new column for your month.

 

 

/*
Return the month number based on a custom start day of month
For example, if inputStartDayofMonth = 25 and inputDate = 1/17/2019 then it will return 1 (January) 
For example, if inputStartDayofMonth = 25 and inputDate = 1/26/2019 then it will return 2 (February)

Parameter
    inputDate: Date for which the month needs to be defined.
    inputStartDayofMonth: the day of the month that is considered as the first day of the month.

Disclaimer: 
   - the date column used must always have a value otherwise an error will be thrown
   - This function won't work yet with a inputStartDayofMonth of 31

homepage: <>
*/

let
    SetCustomMonth = (inputDate as date, inputStartDayofMonth as number) as number =>
        let
            //get day in Gregorian calendar month
            DayofMonth = Date.Day(inputDate),

            //Year input date
            YearInputDate = Date.Year(inputDate),

            //Month input date
            MonthInputDate = Date.Month(inputDate), 

            //Month input date + 1 month 
            MonthInputDatePlus1mth = Date.Month(Date.AddMonths(inputDate,1)),

            //DayofMonthBased is the difference in day between the inputDate and the start periode 
            CustomMonth =  
            if 
                DayofMonth >= inputStartDayofMonth 
            then 
                MonthInputDatePlus1mth
            else     
                MonthInputDate  
        in
            CustomMonth
in
    SetCustomMonth

 

 

Did I answer your question, please mark my post as a solution

If you liked my solution, please give it a thumbs up.

Thank you and keep up the good job!

 

Kind regards,

Lohic Beneyzet