topic EOWEEK in place of EOMONTH for Interval in DAX Commands and Tips

EOWEEK in place of EOMONTH for Interval

Oodi — Fri, 24 May 2024 10:06:38 GMT

Hello,

Inspired from Gustaw Dudeks custom interval chart
(Goodly video (176) Make this Creative & Insightful Line Chart in Power BI 📈 - YouTube)

I have a dataset which has only four months of data and would like to use weekly interval instead of monthly.
The DAX uses EOMONTH function for setting the correct points.

As there is no direct function for EOWEEK, what alternatives could there be to convert the below DAX to weekly?

Monthly Interval Points = var vCurrentMonth = EOMONTH(MAX(DimIntervalCalendar[Date]), 0) var vInterval = [Interval Range] var vIntervalLenght = DATESBETWEEN(DimIntervalCalendar[Date], EOMONTH(vCurrentMonth, - vInterval) +1, vCurrentMonth) var vIntegratedCalendars = CALCULATETABLE(DimCalendar, TREATAS(vIntervalLenght, DimCalendar[Date])) var vTop = DATESBETWEEN(DimCalendar[Date], EOMONTH(vCurrentMonth, - vInterval) + 1, EOMONTH(EOMONTH(vCurrentMonth, - vInterval) + 1, 0)) var vBottom = DATESBETWEEN(DimCalendar[Date], EOMONTH(vCurrentMonth, - 1) + 1, vCurrentMonth) var vIntegrate = UNION(vTop, vBottom) var vMaxExpression = CALCULATE(MAXX(ALLSELECTED(DimCalendar[MMM]), [Expression Stage]), vIntegratedCalendars) var vTotalExpression = CALCULATE([Expression Stage], vIntegrate, KEEPFILTERS(DimCalendar)) return IF(vTotalExpression, vMaxExpression * 1.3)

Re: EOWEEK in place of EOMONTH for Interval

some_bih — Fri, 24 May 2024 10:09:49 GMT

Hi Oodi try to use WEEKNUM function, check link for example

Re: EOWEEK in place of EOMONTH for Interval

lbendlin — Sat, 25 May 2024 12:36:10 GMT

Weeks and months are incompatible. Make sure your users understand the difference.

Re: EOWEEK in place of EOMONTH for Interval

Oodi — Sun, 26 May 2024 14:33:45 GMT

Was able to convert the Line chart to use weeks instead of months with the following DAX:

Interval Weekly Points = VAR _MaxWeek = MAX(DimIntervalCalendar[EndofWeek]) VAR _MaxMonth = EOMONTH(MAX(DimIntervalCalendar[Date]), 0) VAR _Interval = [Interval Range] VAR _Calendar = DATESBETWEEN(DimIntervalCalendar[Date], _MaxWeek - (_Interval * 7) + 1, _MaxWeek) VAR _JoinedCalendar = CALCULATETABLE(DimCalendar, TREATAS(_Calendar, DimCalendar[Date])) VAR _Top = DATESBETWEEN(DimCalendar[Date], _MaxWeek - (_Interval * 7) + 1, _MaxWeek -(_Interval * 7) + 7) VAR _Bottom = DATESBETWEEN(DimCalendar[Date], _MaxWeek - (1 * 7) + 1, _MaxWeek) VAR _Joined = UNION(_Top, _Bottom) VAR _MaxExpression = CALCULATE(MAXX(ALLSELECTED(DimCalendar[WeekofYear]), [Expression]), _JoinedCalendar) VAR _TotalExpression = CALCULATE([Expression], _Joined, KEEPFILTERS(DimCalendar)) RETURN IF(_TotalExpression, _MaxExpression * 1.3)

The interval can stay the same:

Interval Range = GENERATESERIES(1 , 20, 1)

but month-year slicer has to be replaced with week.

DimIntervalCalendar = var vCal = CALENDAR(MIN(Railway[DateofPurchase]), MAX(Railway[DateofJourney])) var vTable = ADDCOLUMNS( vCal, "Sort", YEAR([Date]) & UNICHAR(MONTH([Date]) + 64), "Year Month", FORMAT([Date], "mmm-yy"), "Year", FORMAT([Date], "yyyy"), "Month", FORMAT([Date], "mmm"), "Month Week", FORMAT([Date], "ww-mmm"), "Week", FORMAT([Date], "ww")) return vTable