Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Custom month start and end date dax

Hi experts, I need help to customize my date table.  A production month starts from 25th to 26th. I have added the below table for reference. Need dax formulas for below items Last month Last 3 m...
Need Help
  • hnguy71's avatar
    hnguy71
    4 years ago

    Hi Anonymous ,

     

    I see what you're trying to do. In this case, it is a combination of what CNENFRNL and I have suggested you to do. You need to create that calculated column along with the measures.

    There's a slight data model update required. The expected output is shown below:

     

     

    A sample modified pbix is supplied for you: https://1drv.ms/u/s!An8CCFsOzw0uhQpWhPjO_ua_gE1C?e=UR4kGE

Anonymous's avatar
Anonymous
Not applicable
4 years ago

hnguy71 

hnguy71 thanks for this wonderful solution.

ytd for jan month is showing wrong, we need inculde 26-2021 to 31-2021 data also.

Month start dates for dates after 25th december should be jan of next year.
Please help

Please help

hnguy71's avatar
hnguy71
Icon for Super User rankSuper User
4 years ago

Hi Anonymous ,

I have adjusted the dates to include year end dates to roll towards next year. You may use the same link and download the pbix again. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    hnguy71 yes it is working fine, thanks a lot. Can we create a calendar table based on todays date. if the day is < 26, then max date should be today, else max date should be first day of next month. Because data after 25th of may is not showing in report, because remaining days are belongs to next month as per the custom calendar.

  • hnguy71's avatar
    hnguy71
    Icon for Super User rankSuper User
    4 years ago

    Hi Anonymous ,

    I'm glad it is working out for you. To change your calendar table, you would adjust your formula to this:

     

     

    Calendar = 

VAR _Today = TODAY()
VAR _EndDate = IF(DAY(_Today) < 26, _Today, EOMONTH(_Today, 0) + 1) // If day is less than 26, max date should be today, else max date should be first day of the next month
VAR _Auto = FILTER(CALENDARAUTO(), [Date] <= _EndDate)

RETURN

_Auto

     

     

    EDIT: You would also have to adjust your DateSlicer:

    DateSlicer = CALCULATETABLE(DISTINCT('Calendar'[Date]), DAY('Calendar'[Date]) = 1)
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks a lot for your time and effort hnguy71 ,