cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dapperscavenger
Helper IV
Helper IV

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

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.

 

Capture3.PNG 

 

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

 

Capture4.PNG

 

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!

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@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)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
amitchandak
Super User IV
Super User IV

@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)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

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 ?

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)

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors