Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
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
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!
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.
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!
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |