Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 months

ytd production

 

Month NameDate Range
Sep-21August 26th to September 25th
Oct-21September 26th to October 25th
Nov-21October 26th to November 25th
Dec-21November 26th to December 25th
Jan-22December 26th to January 25th
Feb-22January 26th to February 25th

 

Please help

1 ACCEPTED SOLUTION

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:

 

hnguy71_0-1653836569868.png

 

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



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

View solution in original post

12 REPLIES 12
CNENFRNL
Community Champion
Community Champion

Simple enough, add a calculated column in relation to "Production Year-Month" to the calendar table,

CNENFRNL_0-1653762125173.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension!

DAX is simple, but NOT EASY!

Anonymous
Not applicable

@CNENFRNL yes, but how we can calculate above measures using this calculated column 

hnguy71
Memorable Member
Memorable Member

@Anonymous ,

 

I'm assuming your first column is a date table. Try this formula as it should similarly work for all three expressions (with minor tweaks):

 

Last (n) Range = 

VAR _CurrDate = MAX('Table'[Date])
VAR _MonthNum = MONTH(_CurrDate)

VAR _Offset = 3 // how many months to show. 1 equals last month, 2 equals last 2 months, 3 equals last 3 months, etc.

VAR _StartDate = 
    IF( _MonthNum = 1, 
        DATE(YEAR(_CurrDate), 1 * _Offset, 1), 
        DATE(YEAR(_CurrDate), MONTH(_CurrDate) - (1 * _Offset), 26)
    )

VAR _EndDate = 
    SWITCH( 
        _MonthNum, 
        1, DATE(YEAR(_CurrDate), 1, 25), 
        12, DATE(YEAR(_CurrDate), 12, 31), 
        DATE(YEAR(_CurrDate), MONTH(_CurrDate), 25)
    )

RETURN

_StartDate & " - " & _EndDate


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
Anonymous
Not applicable

@hnguy71 can you please explain it,  am not familiar with power bi.

Hi @Anonymous ,

 

What are you trying to build with these dates? Do you have a sample model or a base measure that you'd like to use the date range?

 

I am assuming that you only wanted to return the output of ranges of dates. If this assumption is wrong, please explain with sample input and sample output so we can be on the same page.



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
Anonymous
Not applicable

@hnguy71 , Please refer my below pbix file. I want to calculate current month , last 3 months and ytd production based on the custom month start and end dates.

https://drive.google.com/file/d/1R3WZpJD2-KF8fWi2sotRE1B__2fHLv5C/view?usp=sharing

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:

 

hnguy71_0-1653836569868.png

 

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



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
Anonymous
Not applicable

@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

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. 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
Anonymous
Not applicable

@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.

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)


Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
Anonymous
Not applicable

Thanks a lot for your time and effort @hnguy71 , 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.