Project daily Sales based on current MTD sales (with time intelligence)

[MADSS]

Hi guys,

Recreating a Sales Report in PowerBI which is referenced to tables, I have managed to figure out most of the formulas for it, but am stuck on the projection one. 

This is the excel formula = =IF(D$52<=$C$3,D55,C67+$C$19)

D52 = the tables day in the month ( eg - 1,2,3,4,5,6,etc)

C3 = The current day (eg todays date day)

D55 = the sales amount for that day of the month 

C67 = The same formula as above, but referencing prior days formula (eg =IF(C$52<=$C$3,C55,B67+$C$19))

C19 = Current Day average sales (eg total sales for the month / Number of days in month (31/30))

Does anyone know how I could immulate this to get the running total of its projection for a graph and also have that days projected figure in a table (this table would be updated daily, to show figures the current day daily). 

Basically this is the graph

and this is basically the tables it references but only for day 1 - the actual table does have columns and figures for everyday of the month. 

I am struggling with the date intellegence of it all. 

I basically need it to store figures for everyday of the month. 

Formula translates to: if day in table is smaller than the current day, then show the sales figure for that day, else add yesterdays projected figure and the average daily sales figure. 

(eg all projected figures before the current date should be the same as the sales figure, and all projected figures after the current date should be the "projected" figure plus the current average daily sales for the month (eg total sales / months current day)

Hope this makes sense. 

[v-yanjiang-msft · Accepted solution]

Hi @MADSS ,

From your description in the latter part, I create a sample.

As today is 2022/5/31, so the result of 2022/5/1-2022/5/30 are value in the Sales column, the result of 2022/5/31 is yesterdays projected figure(3) + the average daily sales figure(15.5)=18.5. Here's my solution.

Create a calculated column.

Column =
VAR _Y =
    DATEADD ( 'Table'[Date], -1, DAY )
RETURN
    IF (
        'Table'[Date] < TODAY (),
        'Table'[Sales],
        MAXX ( FILTER ( 'Table', 'Table'[Date] = _Y ), 'Table'[Projected] )
            + AVERAGEX (
                FILTER (
                    'Table',
                    MONTH ( 'Table'[Date] ) = MONTH ( TODAY () )
                        && 'Table'[Date] < TODAY ()
                ),
                'Table'[Sales]
            )
    )

Get the correct result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[lbendlin]

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523