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 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 ,