Forum Discussion

darylmc's avatar
darylmc
Frequent Visitor
6 years ago
Solved

Measure - same day last week per column

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:
 
ShopProductAvail
AA24%
 B50%
 C76%
 Total24%
BA12%
 B20%
 C28%
 Total

12%

 

When I would expect something like this:

 

ShopProductAvail
AA24%
 B50%
 C76%
 Total50%
BA12%
 B20%
 C28%
 Total20%

 

Any help appreciated,

 

Thanks

Need Help
  • sturlaws's avatar
    sturlaws
    6 years ago

    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

3 Replies