Forum Discussion

PwrBI01's avatar
PwrBI01
Icon for Post Patron rankPost Patron
5 years ago

Measure doesn't work correctly with slicer

Hi guys,

 

I am trying to create a forecast for a period not ended. The measure is the next one:

Forecast = (Sales 12 last months / Sales 12 previous months * Total sales previous year) - Year-to-date sales

 

So, if the selected period is June 2020 the variables will be the following ones:

1. Sales 12 last months = Sales from July 2019 to June 2020

2. Sales 12 previous months = Sales from July 2018 to June 2019

3. Total sales previous year = Sales from January 2019 to December 2019

4. Year-to-date sales = Sales from January 2020 to June 2020

 

The problem is that when I introduce the measure on a chart and put a slicer for year 2020, the variables 'Sales 12 last months', 'Sales 12 previous months' and 'Total sales previous year' show a wrong result, because it doesn't take into account the sales from 2018 and 2019, so for example the result for 'Sales 12 previous months' is 0.

 

How can I solve this?

 

Thanks in advance.

Need Help

11 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    PwrBI01 , Use month in the slicer from the date table and try measures like

     

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
    Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
    This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31")) //complete
    Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) //complete

    Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

     

     

    //Only year vs Year, not a level below

    This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
    Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

     

    Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH)) //last 12

    // 12 before 12

    Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))
    Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd(Sales[Sales Date],-12,month)),-12,MONTH))
    Rolling 12 till last 12 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd('Date'[Date],-12,month)),-12,MONTH))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

    • PwrBI01's avatar
      PwrBI01
      Icon for Post Patron rankPost Patron
      5 years ago

      Hi amitchandak, thank you for your answer.

       

      I have tried that way but it doesn't work.

    • PwrBI01's avatar
      PwrBI01
      Icon for Post Patron rankPost Patron
      5 years ago

      Hi Anonymous and Ashish_Mathur, 

       

      Thank you for your answers.

       

      I send you the link where the Power BI file is located so you can download it.

       

      https://drive.google.com/file/d/1qRQ4cGYELIraQ1IzhVXeNsiCJZO46U-r/view?usp=sharing

       

      I have created 2 columns, one for the results I obtain when I put the measure on a target and another column for the results I get when I filter the same measures for the year 2019 (I think this are the results the chart is showing) for trying to help you to understand.

       

      Thanks in advance.

       

      Regards.