Rolling 4 week and 12 week average with formated dates

[Anonymous]

Hi all, 

I have an issue that I simple can't solve. It is about calculating the rolling average of a measure, that works with my standard date table, however fails to work with my dynamic date table. The dynamic date table allows me to switch between month, weeks and days in every graph, but it fails to calculate the rolling average as I formated the visual date on the x-axis. Let me explain by example.

My rolling average measure:

4 week rolling average = 
VAR NumOfDays = 4*7
VAR LastCurrentDate =
    LASTDATE ( 'dynamic date'[Date] )
VAR Period =
    DATESINPERIOD ('dynamic date'[Date], LastCurrentDate, - NumOfDays,DAY )
VAR Result =
    CALCULATE (
        AVERAGEX (
            VALUES ( table[date] ),
            table[measure]
        ),
        Period
    )
VAR FirstDateInPeriod = MINX ( Period, 'dynamic date'[Date])
VAR LastDateWithSales = MAX ( 'dynamic date'[Date] )
RETURN
    IF ( FirstDateInPeriod <= LastDateWithSales, Result )

The same was done with the 12 week rolling average with the only difference in the number of days. My dynamic date table has three rows for each date (one that displays formated month, week and day that I wish to have on my x-axis). It looks like this:

My dynamic date table and my ordinary date table has a many-to-one relationship that works on all other measures expect for the rolling average. 

On the left graph you see that my rolling average works perfectly with the dates from my ordinary calender table, however when replaced with my formated dates then the rolling avg fails on a row level and the difference between 4 week and 12 week is 0.

I hope someone can help me with this issue.. 

Best,

NicoM96

[tamerj1 · Accepted solution]

Hi @Anonymous 

DATESINPERIOD is a time intelligence function that works only with standard date table. You may try

4 week rolling average =
VAR NumOfDays = 4 * 7
VAR LastDateInPeriod =
    MAX ( 'dynamic date'[Date] )
VAR FirstDateInPeriod = LastDateInPeriod - NumOfDays
VAR LastDateWithSales =
    CALCULATE ( MAX ( table[date] ), REMOVEFILTERS () )
VAR Result =
    CALCULATE (
        AVERAGEX ( VALUES ( table[date] ), [measure] ),
        REMOVEFILTERS ( 'dynamic date' ),
        'dynamic date'[Date] > FirstDateInPeriod,
        'dynamic date'[Date] <= LastDateInPeriod
    )
RETURN
    IF ( LastDateInPeriod <= LastDateWithSales, Result )

[Anonymous]

Important to note is that the right graph are showing "visual date" (text format) from my dynamic date table, but have been renamed to "formated date".  Anyone know the solution for this?