Tricky Dax funcdtion

[Dankang]

Hi All

Hope anyone can help me with this.

Outlet ID refers to stores

Number value refers to the number of displays field reps have built in the store

Basially it's assmued that once field reps have built displays the displays they have built will still be up in the store until the next visit. So i want to have the prior week's number of displays if reps don't visit the store in the follwoing weeks.

i.e Outlet ID 1000245 although a rep didnt visit the store on the 27th August i want a value of 2 from the prior week and the same number for the follwoing weeks until they visit the store and amend the value again.

Then eventually i want a sum of total displays each week.

Any advice or help would be greatly appreciated.

Cheers

[SteveCampbell]

The issue is that, I assume, in your data there is actually missing valuesa for the blanks. This is an issue as that means there is actually a nonexistant row.

The *BEST* way, in my opion, would to create a date table. Easiest way is to go to New Table:

Date = CALENDARAUTO()

Or, by using Calendar function and passing parameters.

You can use this Measure:

Weekly Count = 
var _date = SELECTEDVALUE('Date'[Date],BLANK())
var _maxDateValue = 
CALCULATE(MAX(Table1[Date])
    ,ALLEXCEPT(Table1,Table1[OutletID])
    ,Table1[Date]<=_date)
    
RETURN
    
CALCULATE(SUM(Table1[Value])
    ,ALLEXCEPT(Table1,Table1[OutletID])
    ,'Date'[Date]=_maxDateValue)

I've highlighted in red names you may need to change as were not available in your question.

Now, replace your date column in your matrix with 'Date'[Date], and the Value with the new measure Weekly Count.

This will show every day. In order to just show the weeks, you can make a Column in the date table :

Include  = 
//checks whether this date is in you original table with data
var _date = 'Date'[Date]

return
IF(CALCULATE(SUM(Table1[Value]),'Table1'[Date]=_date)>0,1,0)

You can then add this column into the visual level filters, and set as 1.

This is a little more complicated, but lets you apply date logic and date filters. One thing, it will not show total for OutletID. I think in the context of what it is measuring, from my understanding of the question, this makes sense as a sum of a weekly count of outlets would not be relevant (you could just use an AVERAGE function, which would make more sense).

[v-frfei-msft]

Hi @Dankang,

Does that make sense? If so, kindly mark the answer as a solution to close the case.

Regards,
Frank