Forum Discussion
Planned sales calculation
Hello all,
I'm learning Power BI and I need your help please with my expression.
I have two columns I'm using and I'd like to build a measure called NouvelleValeur.
- Mnozstvi Prodej SRO = Sales data
- Mnozstvi NAV (palety) = Forecast data
- NouvelleValeur = Planned sales (measure calculating the landing of sales for current / future months)
I'd like to create this measure for calculating the Planned sales.
So the three cases the measure "NouvelleValeur" should handle:
- Future period (May 2025, June 2025, etc.): For each future month, the calculation must calculate the ratio of previous three months (last 3 months of the future month) sales/forecast, multiplying by the Mnozstvi NAV of the future period.
- Period equal to today (March 2025): The calculation follows the same principle as future period.
- Period in the past (January 2025, February 2025): The value returned is 0.
I tried to make a formula, but i think it's only calculating 3 previous months data for current month/year, and unfortunately I'm not yet comfortable enough with it.
Probably it would be easier to calculate two different measures calculating for each month past 3 months' sales and forecast ?
Could someone help me please ?
Thank you and have a nice day,
Best regards.
Hi LeJardinier ,
To create the measure NouvelleValeur in Power BI, you need a DAX formula that distinguishes between past, current, and future periods. The measure should return 0 for past months, apply a ratio-based calculation for the current month, and use the same ratio logic for future months. The calculation relies on retrieving sales and forecast data for the last three available months and applying the ratio to the forecast value of the given period.
NouvelleValeur = VAR CurrentMonth = MAX('Database'[Month/Year]) VAR TodayMonth = FORMAT(TODAY(), "dd-mmm") // Adjust format if necessary VAR IsPast = CurrentMonth < TodayMonth VAR IsFuture = CurrentMonth > TodayMonth VAR SalesLast3 = CALCULATE( SUM('Database'[Sales (Mnozstvi Prodej SRO)]), DATESINPERIOD('Database'[Month/Year], CurrentMonth, -3, MONTH) ) VAR ForecastLast3 = CALCULATE( SUM('Database'[Forecast (Mnozstvi NAV (palety))]), DATESINPERIOD('Database'[Month/Year], CurrentMonth, -3, MONTH) ) VAR CurrentForecast = SUM('Database'[Forecast (Mnozstvi NAV (palety))]) VAR PlannedSales = IF( ForecastLast3 > 0, (SalesLast3 / ForecastLast3) * CurrentForecast, BLANK() ) RETURN IF(IsPast, 0, PlannedSales)The formula first identifies the current month and determines whether it falls in the past or future relative to today. It then calculates the sum of sales and forecast values for the past three months. Using these values, it computes the ratio of sales to forecast and applies it to the current period’s forecast value. If the forecast for the past three months is greater than zero, the ratio is applied; otherwise, the measure returns blank. Finally, if the period is in the past, the measure returns 0; otherwise, it returns the planned sales value.
Best regards,
2 Replies
- DataNinja777
Super User
Hi LeJardinier ,
To create the measure NouvelleValeur in Power BI, you need a DAX formula that distinguishes between past, current, and future periods. The measure should return 0 for past months, apply a ratio-based calculation for the current month, and use the same ratio logic for future months. The calculation relies on retrieving sales and forecast data for the last three available months and applying the ratio to the forecast value of the given period.
NouvelleValeur = VAR CurrentMonth = MAX('Database'[Month/Year]) VAR TodayMonth = FORMAT(TODAY(), "dd-mmm") // Adjust format if necessary VAR IsPast = CurrentMonth < TodayMonth VAR IsFuture = CurrentMonth > TodayMonth VAR SalesLast3 = CALCULATE( SUM('Database'[Sales (Mnozstvi Prodej SRO)]), DATESINPERIOD('Database'[Month/Year], CurrentMonth, -3, MONTH) ) VAR ForecastLast3 = CALCULATE( SUM('Database'[Forecast (Mnozstvi NAV (palety))]), DATESINPERIOD('Database'[Month/Year], CurrentMonth, -3, MONTH) ) VAR CurrentForecast = SUM('Database'[Forecast (Mnozstvi NAV (palety))]) VAR PlannedSales = IF( ForecastLast3 > 0, (SalesLast3 / ForecastLast3) * CurrentForecast, BLANK() ) RETURN IF(IsPast, 0, PlannedSales)The formula first identifies the current month and determines whether it falls in the past or future relative to today. It then calculates the sum of sales and forecast values for the past three months. Using these values, it computes the ratio of sales to forecast and applies it to the current period’s forecast value. If the forecast for the past three months is greater than zero, the ratio is applied; otherwise, the measure returns blank. Finally, if the period is in the past, the measure returns 0; otherwise, it returns the planned sales value.
Best regards,
