Trend Error

[barlevitzky]

Hi All,

I want to check the searches trend of every product in according to the same day of the week last week. 

I have written the next measure:

Searches Trend = DIVIDE([Total Searches],CALCULATE([Total Searches],DATEADD('Extensions data'[Date],-7,DAY)))

My data is written in a way which if there are no searches at a particular day, there is no row. I can't change that. 

So, when I try to create a table, the smaller product might not have a row on the same day last week so I get this error:

How can I overcome this error?

I prefer to get a 0 on the trend. 

Thanks

[v-juanli-msft]

Hi @barlevitzky

Since i don't konw how the measure [Total Searches] gets and what your data displays, I can't reproduce this issue.

For my data which some values have no row on the same day last week within the current table, i create a measure like this and it works

Measure 1 =
VAR sevendaysago =
    CALCULATE (
        SUM ( Table13[search] ),
        FILTER (
            ALL ( Table13 ),
            DAY ( Table13[Date] )
                = DAY ( MAX ( Table13[Date] ) ) - 7
        )
    )
RETURN
    IF ( sevendaysago = BLANK (), 0, DIVIDE ( [Total Searches], sevendaysago ) )

Best Regards

Maggie