Forum Discussion

amarada's avatar
amarada
Regular Visitor
8 years ago
Solved

Calculate the closing estimate

Hi, 

 

I need to create a measure to divide the num days of a month/NUMBER OF DAYS WITH SALES DATA, ir order to calculate a closing estimate. Fe: November has 30 days and I know the sales for the fisrt 15 days (30/15) end finally I multiply by the monthly total sales... (total number of days of the month/number of days I have sales)*total sales of the current month.  

Any idea? 

Thanks so much

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    8 years ago

    Tough to be specific here without sample data but perhaps something along the lines of:

     

    Sales Data = CALCULATE(SUM(Sales[Sales]),FILTER(Sales,[Sales]<>BLANK()))

    ?

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Tough to be specific here without sample data but perhaps something along the lines of:

     

    Sales Data = CALCULATE(SUM(Sales[Sales]),FILTER(Sales,[Sales]<>BLANK()))

    ?

  • amarada's avatar
    amarada
    Regular Visitor
    8 years ago

    Thank you very much for your help. I have adapted the measurements and clues that you have given me and, after several attempts, it is already working

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Measure:

    Estimated Sales at EOM =

    VAR DaysWithSales=distinctcount('sales'[date])
    VAR DaysInMonth = distinctcount('datetable'[date])
    RETURN
    SUM('sales'[salesamount])/
    DIVIDE(DaysWithSales,DaysinMonth,0)

     

    I think something like this would work if you have a date table linked to your sales table.  If you then create a table where the month/year is a dimension, it should calculate it correctly.