How to create measure which shows the length of time since last sold

[dapperscavenger]

I want to show the products which meet the following crietria:

• There must have been some sales in the past (total demand over all time > 0)
• There must be some forecast in the future (total forecast over all time >0)

I want to show the length of time since the item was last sold.  For example, the product in the table below last sold June 2019, so length of time is 11 months ago. It also has a forecast.  It is not important when the forecast is or how much it is, only that there is any at all.

 

The model looks like this.  The date in the Fact table is not a good date format.  The DimDate[LongMonthYear] must be used.

The current formulas are:

SelectSalesActuals = CALCULATE(
        SWITCH(TRUE(),
        VALUES(Z_UoM[UoM]) = "Volume EU", sum('Fact_L8'[Billed Sales EU]),
        VALUES(Z_UoM[UoM]) = "Volume AC", sum('Fact_L8'[Billed Sales AC] ),
        VALUES(Z_UoM[UoM]) = "Volume 9L", sum('Fact_L8'[Billed Sales 9L]),
        sum('Fact_L8'[Billed Sales HL])),
    FILTER(DimDate, DimDate[LongMonthYear] <  DATE(YEAR(TODAY()), MONTH(TODAY()), 1)  ))

SelectForecast = CALCULATE(
        SWITCH(TRUE(),
        AND(VALUES('Z_Forecast'[Forecast]) = "LPF", VALUES(Z_UoM[UoM]) = "Volume EU"), sum('Fact_L8'[LPF EU]),
        AND(VALUES('Z_Forecast'[Forecast]) = "LPF", VALUES(Z_UoM[UoM]) = "Volume AC"), sum('Fact_L8'[LPF AC]),
        AND(VALUES('Z_Forecast'[Forecast]) = "LPF", VALUES(Z_UoM[UoM]) = "Volume 9L"), sum('Fact_L8'[LPF 9L]),
        AND(VALUES('Z_Forecast'[Forecast]) = "LPF", VALUES(Z_UoM[UoM]) = "Volume HL"), sum('Fact_L8'[LPF HL]),

        AND(VALUES('Z_Forecast'[Forecast]) = "FTBP", VALUES(Z_UoM[UoM]) = "Volume EU"), sum('Fact_L8'[FTBP EU]),
        AND(VALUES('Z_Forecast'[Forecast]) = "FTBP", VALUES(Z_UoM[UoM]) = "Volume AC"), sum('Fact_L8'[FTBP AC]),
        AND(VALUES('Z_Forecast'[Forecast]) = "FTBP", VALUES(Z_UoM[UoM]) = "Volume 9L"), sum('Fact_L8'[FTBP 9L]),
        AND(VALUES('Z_Forecast'[Forecast]) = "FTBP", VALUES(Z_UoM[UoM]) = "Volume HL"), sum('Fact_L8'[FTBP HL]),

        AND(VALUES('Z_Forecast'[Forecast]) = "WD20", VALUES(Z_UoM[UoM]) = "Volume EU"), sum('Fact_L8'[WD20 EU]),
        AND(VALUES('Z_Forecast'[Forecast]) = "WD20", VALUES(Z_UoM[UoM]) = "Volume AC"), sum('Fact_L8'[WD20 AC]),
        AND(VALUES('Z_Forecast'[Forecast]) = "WD20", VALUES(Z_UoM[UoM]) = "Volume 9L"), sum('Fact_L8'[WD20 9L]),
        sum('Fact_L8'[WD20 HL])
    ),
    FILTER(DimDate, DimDate[LongMonthYear] >=  DATE(YEAR(TODAY()), MONTH(TODAY()), 1)  ))

Any help on how to create this formula to give me the result I need?

Thank you!

[amitchandak · Accepted solution]

@dapperscavenger ,

With Help SKU from DImSKU you can have measure like

example measure

datediff(calculate(max(Fact_l18[date]),allexcept(DImSKU,DImSKU[SKU])), today(), day)

[dapperscavenger]

One more question, is it possible to add a filter in this?

I am getting some nagtive values when I have forecast.  I only want to know when it was last sold. i.e. when Sales >0

Is it possible to add a filter like Fact_L8[Billed Sales EU] > 0 ?

[dapperscavenger]

Thank you that worked!  I tweaked it a little 🙂

LastSalesL6 = 
datediff(
    CALCULATE(MAX(Fact_Monthly[Date]), FILTER(Fact_Monthly, Fact_Monthly[Billed Sales EU]>0)), 
    today(), 
    MONTH)