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.

ChrisMendoza's avatar
ChrisMendoza
Resident Rockstar
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.

 

cedmiston's avatar
cedmiston
Frequent Visitor
6 years ago

This worked great thank you!

There was one bug in that the end of November (25-30) showed up as 0 instead of 1-12, but I just added this to the switch formula as well to populate as our "December".