show actual, forecast numbers in a single visualization

[89898]

Hello All,

I have a question on building the formulas in order to display the actual sales from Jan – August, next month forecast (September), plan number from Oct to December as per the chart above.

Please anyone can advise what kind of formulas or logic that I need to apply in order to combine all these values in one bar chart? The challenges are these are coming from 3 different tables and I have created three measurement separately for MTD sales, MTD NMF sales and MTD Plan and I have the full years data for these, but what formulas I should apply in order to only show the months/values that I would like to show in the dashboard as per requirement below?

Appreciate the help, thanks in advance!

[v-shex-msft]

Hi @89898,

You can write different calculate formulas for different calculation logic. Then use if statement to choose which measure work based on month.

Sample:

Measure =
VAR currDate =
    MAX ( Table[Date] )
RETURN
    IF (
        MONTH ( CurrDate ) < 9,
        SUM ( Table[Amount] ),
        IF ( MONTH ( CurrDate ) = 9, 'Forecast formula', 'Plan formula' )
    )

Regards,

Xiaoxin Sheng

[89898]

Thanks @v-shex-msft But in this case, i will need to update the number "9" on a monthly basis? example in September, i will need to update the numbers from 9 to 10. is there a way that i dont need to change the number manually every month?

thanks in advance!

[gselvag]

We resolved this issue by created a seperate measure that is evaluated for current period (e.g. september) instead of static month values. This measure is dynamic and used as a slicer, so that you can select any month and display flexible actual-forecast.   

[89898]

Hi Gselvag, appreciate if you can show detail steps on how to do this? sorry as i am really new in Power BI DAX.

Many thanks!