Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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!
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |