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 f...
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()))

    ?

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.