SIMPLE forecast Dax measure

[James__]

Hi All,

Hoping  you can help as I'm exhausted looking at this one.

I have a targets report that has to include forecasting based on the day slicer (month/quarter/year).

They want the forecase to be sales_per_day * days_left_inmonth/quarter/year + sales_per_day

I can get sales per day: Divide(SUM)[sales],COUNTROWS(calendar)) BUT how do i work out how many days are left in the period?

Sorry i am sure this is fairly obvious - it has been a long day!

[v-jingzhang · Accepted solution]

Hi @James__ 

Can you share some sample data and expected result in table format? To calculate how many days are left in the period, we need to know which date is the end date of a period and which date is the start date. If you select a month from the slicer, I guess probably you want to calculate the forecast result from the last sales date to the month end? If so, you can try a measure like below. 

left days =
VAR _lastSalesDate = CALCULATE ( MAX ( 'sales'[Date] ), ALL ( 'sales' ) )
RETURN
    DATEDIFF ( _lastSalesDate, MAX ( 'calendar'[Date] ), DAY )

This assumes that the last sales date is earlier than the period end date. If this is not what you want, please share some sample data or a sample pbix, this will help us understand your requirement better. How to provide sample data in the Power BI Forum

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!