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

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 ,