Rolling 3 mos when spans end of previous yr and beginning of current yr; sorting issue as well

[iDataDrew]

I don't have sample data to share, but any possible solutions based on your past experience would be appreciated.

I have to write a rolling 3 month sum measure, but I have to keep in mind that on January of the current year (for example) it will have to show November and December of the previous year.  I think the following filtering would work:

DATESINPERIOD ( table[date], TODAY(), -3, MONTH)

However, I think the problem I'm running into is that the x-axis is the end of week (week-based).  I created this end of week columns as follows:

EndOfWeek = FORMAT(DimDate[Date]+(6-WEEKDAY(DimDate[Date])),"MMM-DD")

But I can't seem to properly sort this column by the Date column due to the error that there can't be more than one value in the Date column for the same value in the EndOfWeek column.  So maybe this is more of a sorting question for this kind of column.

[Phil_Seamark]

Hi there

Please try creating the following extra column to us as a sorting col.


EndOfWeekID = INT (FORMAT(DimDate[Date]+(6-WEEKDAY(DimDate[Date])),"YYMMDD"))

And use this as your sort by column for your EndOfWeek

[iDataDrew · Accepted solution]

@Phil_Seamark I got the same error.  This is because the way I have the FORMAT() make the EndOfWeek dates show as month and day, which means there are duplicate values in the column (one for each year).  I just took away the FORMAT() from the EndOfWeek column so that it displays as a regular date, which means there will be distinct values.  This solves the sorting issue.  Then I just selected the date formatting option from the menu bar to display as MMMM DD.