trouble with date subtraction

[Doby42]

I'm trying to look at a rolling 4 weeks, so I created a sum with filter of max[date] and max[date] - 28 for my filter range.  It wasn't working so I broke down the elements and i'm at the -28 date.  it doesn't seem to work.  I tried to do a DATE(year, month, day) and that didn't work, finding out just nomral max[date] - 28 breaks my output:

if I put in

!MaxDateMin28 = DAY(MAX(cwtime[Date_Start]))

I get an output of:

!MaxDate: 01/04/19

iMaxDateMin28: 4

if I put in 

!MaxDateMin28 = DAY(MAX(cwtime[Date_Start])) - 1

I get a unfilterd list of weeks, so for most of it:

!MaxDate: [blank]

iMaxDateMin28: -1

I tried without bothering with DAY and that resulted in date of 12/29/1899 12:00:00 AM

any help on how I can create a filter for 4 weeks range?

[Ashish_Mathur]

Hi,

Create a Calendar Table and build a relationship from the Date_Start column of the cwtime Table to the Date column of the Calendar Table.  To your visual, drag Date from the Calendar Table.  Write this measure

=CALCULATE(SUM(Data[Value]),DATESBETWEEN(Calendar[Date],MIN(Calendar[Date])-28,MIN(Calendar[Date])))

Hope this helps.

[Doby42]

I have tried that, I do have a date table and orginally referenced that table in the query instead.

I would be willing to try again if you think it would matter, but since the date in this table is a date field, its should work.  Further, functions like DAY on that filed do return the proper result.

What it seems to be doing is killing the filter on my output table, hence showing all results for all days, even though the list should only be for a narrow date range.  Because of this it is apply the -1 to a blank field as that was filtered out.

Seems a bit odd, I'm able to work around it with an additional IF(BLANK) statement before I apply the -1, but I'm confused what that is necessary.

[Ashish_Mathur]

Please try again.

[Doby42 · Accepted solution]

actually, thats worse.  when I use my date table then it breaks the filter and shows all dates in the matrix regardless if I try to subtract days.  When I use the date field in the table I'm working with it works fine as long as I don't subtract, adding the subtract breaks the filter and shows all dates with lots of empty fields.

Currently I have it giving the desired results with:

!MaxDateMin28 = IF(MAX(cwtime[Date_Start]) <> BLANK(), MAX(cwtime[Date_Start]) - 28)