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
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors