Cumulative projected sales determined by start and end of sale as tooltip

[PJHos]

Hi all and happy new year,

I am trying to create a tooltip that will allow me to plot cumulative actual sales vs dynamic cumulative expected sales - i would really like the expected cumulative sales to be a dynamic figure based on current sales minus target / remaining period. M

I have the following tables

'SalesReportUS'

'StoreReportUS'

'CalenderUSReport'

I have used the following DAX to give me the cumulative sales

CALCULATE( [Total sales] ,

    FILTER( ALL ( 'CalenderUSReport'[Sales.[Date] ),

                'CalenderUSReport'.[Date] <=  MAX('CalenderUSReport'.[Date] ) ) )

And the following DAX to give me an projected monthly sales figure to achieve our targets ( seperate table )

Created - monthly sales target figure =

VAR StoreFilter = IF( OR ( ISBLANK ( 'StoreReportUS'[Saleopening.[Date]), ISBLANK('StoreReportUS'[Saleclosing.[Date])), 0, 1)

VAR Monthsofsale = DATEDIFF ( 'StoreReportUS'[Saleopening.[Date], 'StoreReportUS'[SaleClosing.[Date], MONTH)))

VAR ExpectedMonthlySales= IF ( StoreFilter = 1, [Total tsales] /  Monthsofsale )

VAR RESULT = ExpectedMonthlySales

   RETURN

      RESULT

Cumulative Expected Monthly Sales =

CALCULATE ( [Total expected monthly sales] ,

    FILTER ( ALL  'CalenderUSReport'.[Date]),

                  'CalenderUSReport'.[Date] <=  MAX ( 'CalenderUSReport'.[Date] ) ) )

I have then just used the above cumulative measure with this result in an attempt to get a cumulative monthly figure. Needless to say this hasnt worked and i cannot figure this out.

I would be really grateful for some help or advice 😥

[MatthRichardsUK · Accepted solution]

Ok, so you have created two measures to calculate expected sales: [Created - monthly sales target figure] and [Cumulative Expected Monthly Sales]. The first measure calculates the expected monthly sales for each store and the second measure calculates the cumulative expected monthly sales for all stores up to the current date.

To create a dynamic expected sales curve that updates based on current sales and remaining period, you can create a new measure that calculates the expected sales for each date using the following formula:

Expected Sales = VAR StoreFilter = IF( OR ( ISBLANK ( 'StoreReportUS'[Saleopening.[Date]), ISBLANK('StoreReportUS'[Saleclosing.[Date])), 0, 1) VAR Monthsofsale = DATEDIFF ( 'StoreReportUS'[Saleopening.[Date], 'StoreReportUS'[SaleClosing.[Date], MONTH))) VAR CurrentSales = CALCULATE([Total sales], 'CalenderUSReport'[Date] <= MAX('CalenderUSReport'[Date])) VAR TargetSales = [Total tsales] VAR RemainingPeriod = Monthsofsale - DATEDIFF( 'CalenderUSReport'[Date], 'StoreReportUS'[Saleopening.[Date]], MONTH) VAR ExpectedSales = IF(StoreFilter = 1, TargetSales * CurrentSales / (RemainingPeriod * TargetSales)) RETURN ExpectedSales

This measure calculates the expected sales for each date by using the current sales and the remaining period to calculate a projected sales curve that will achieve the target sales. You can then use this measure to create a line chart in Power BI that plots the actual sales and expected sales over time.

I hope this helps! Let me know if you need any further assistance. Don't forget to mark it as solved if my solution helps you out.

[MatthRichardsUK · Accepted solution]

Yes, the error you are seeing is likely due to the fact that the 'CalenderUSReport' table is connected to the 'SalesReportUS' table and not the 'StoreReportUS' table.

To fix this error, you can modify the formula as follows:

Expected Sales =
VAR CurrentSales = CALCULATE([Total sales], 'CalenderUSReport'[Date] <= MAX('CalenderUSReport'[Date]))
VAR StoreFilter = IF( OR ( ISBLANK ( 'StoreReportUS'[Saleopening.[Date]), ISBLANK('StoreReportUS'[Saleclosing.[Date])), 0, 1)
VAR Monthsofsale = DATEDIFF ( 'StoreReportUS'[Saleopening.[Date], 'StoreReportUS'[SaleClosing.[Date], MONTH)))
VAR TargetSales = [Total tsales]
VAR RemainingPeriod = Monthsofsale - DATEDIFF( 'CalenderUSReport'[Date], 'StoreReportUS'[Saleopening.[Date]], MONTH)
VAR ExpectedSales = IF(StoreFilter = 1, TargetSales * CurrentSales / (RemainingPeriod * TargetSales))
RETURN
ExpectedSales

By moving the calculation of the current sales to the beginning of the formula, you ensure that it is calculated before the other variables that depend on it. This should fix the circular dependency error.

I hope this helps! Let me know if you have any questions or need further assistance.

[PJHos]

I had actually modified it last night after seeing your reply and it worked, thank you again for your help!!

[MatthRichardsUK · Accepted solution]

Ok, so you have created two measures to calculate expected sales: [Created - monthly sales target figure] and [Cumulative Expected Monthly Sales]. The first measure calculates the expected monthly sales for each store and the second measure calculates the cumulative expected monthly sales for all stores up to the current date.

To create a dynamic expected sales curve that updates based on current sales and remaining period, you can create a new measure that calculates the expected sales for each date using the following formula:

Expected Sales = VAR StoreFilter = IF( OR ( ISBLANK ( 'StoreReportUS'[Saleopening.[Date]), ISBLANK('StoreReportUS'[Saleclosing.[Date])), 0, 1) VAR Monthsofsale = DATEDIFF ( 'StoreReportUS'[Saleopening.[Date], 'StoreReportUS'[SaleClosing.[Date], MONTH))) VAR CurrentSales = CALCULATE([Total sales], 'CalenderUSReport'[Date] <= MAX('CalenderUSReport'[Date])) VAR TargetSales = [Total tsales] VAR RemainingPeriod = Monthsofsale - DATEDIFF( 'CalenderUSReport'[Date], 'StoreReportUS'[Saleopening.[Date]], MONTH) VAR ExpectedSales = IF(StoreFilter = 1, TargetSales * CurrentSales / (RemainingPeriod * TargetSales)) RETURN ExpectedSales

This measure calculates the expected sales for each date by using the current sales and the remaining period to calculate a projected sales curve that will achieve the target sales. You can then use this measure to create a line chart in Power BI that plots the actual sales and expected sales over time.

I hope this helps! Let me know if you need any further assistance. Don't forget to mark it as solved if my solution helps you out.

[PJHos]

Thank you for kindly taking the time to reply

i have used the DAX but the following line returns a cricular dependancy error. is this because the calendar table is connected to the salesreport not the storereport

VAR CurrentSales = CALCULATE([Total sales], 'CalenderUSReport'[Date] <= MAX('CalenderUSReport'[Date]))