Measure - same day last week per column

[darylmc]

Hi All,

I'm looking to create a measure for availability for the same day last week. I've created the following formula which mostly works

LW Avail = calculate(Divide(Sum(History[avail_sum]),Sum(History[avail_count]),""),filter(all

(History),History[Date]=MAX(History[Date])-7),FILTER(All(History),History[Store]=MAX(History[Store])),FILTER(All(History),History[Product]=MAX(History[Product])))

However, as per the formula, the total per store is taking the number of one of the Products.

So it's giving this:

Shop	Product	Avail
A	A	24%
	B	50%
	C	76%
	Total	24%
B	A	12%
	B	20%
	C	28%
	Total	12%

When I would expect something like this:

Shop	Product	Avail
A	A	24%
	B	50%
	C	76%
	Total	50%
B	A	12%
	B	20%
	C	28%
	Total	20%

Any help appreciated,

Thanks

[sturlaws · Accepted solution]

Hi, @darylmc ,

you could write your measure like this:

LW Avail =
VAR _product =
    CALCULATE ( SELECTEDVALUE ( History[Product] ) )
VAR _date =
    CALCULATE ( MAX ( History[Date] ) )
VAR _store =
    CALCULATE ( SELECTEDVALUE ( History[Store] ) )
RETURN
    IF (
        HASONEVALUE ( History[Product] ),
        CALCULATE (
            DIVIDE ( SUM ( History[avail_sum] ), SUM ( History[avail_count] ), "" ),
            FILTER (
                ALL ( History ),
                History[Date] = _date - 7
                    && History[Store] = _store
                    && History[Product] = _product
            )
        ),
        CALCULATE (
            DIVIDE ( SUM ( History[avail_sum] ), SUM ( History[avail_count] ), "" ),
            FILTER ( ALL ( History ), History[Date] = _date - 7 && History[Store] = _store )
        )
    )

Cheers,
Sturla

[darylmc]

Works like a charm!

Thanks

[amitchandak]

Try to create a new measure like

averagex(summarize(table,table[shop],table[Product],"_avg",[LW Avail]),_avg)

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin