Forum Discussion
Custom month start and end date dax
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
Super UserAnonymous ,
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 & " - " & _EndDatehnguy71 can you please explain it, am not familiar with power bi.
- hnguy71
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.
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