Forum Discussion
Helper IStagger Chat in Power BI
Hi Varishtha ,
If you want to use 20 as your forecast sales value for the next month, please take a look at this test:
My Model View:
Calender = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2024,12,31)),"Start of Month",DATE(2024,MONTH([Date]),1),"_Month",MONTH([Date]))
I created another new table:
Table 2 = VALUES('Calender'[Start of Month])
My Report View and Measure value:
Measure = IF(
MONTH( MAX( 'Calender'[Start of Month] ) )=MAX('Calender'[_Month] ) ,
MAX('Calender'[_Month]), 0)
Measure 3 = IF( MONTH( MAX('Calender'[Start of Month])) = MONTH( MAX('Table 2'[Start of Month]) ) ,MONTH( MAX('Calender'[Start of Month])), IF( MONTH( MAX('Calender'[Start of Month])) +1 = MONTH( MAX('Table 2'[Start of Month]) ) , 20 , BLANK()))
Best Regards,
Sunshine Gu
Hi Varishtha ,
Could you please replace the STARTOFMONTH function outside Callender [Start of Month] and the MONTH function outside Callender [_Month] with the Max function in the measure?
I did a test for your reference.
In my scenario:
Since you didn't provide a pbix file, first I assume a table, as shown in the image below:
Here is a screenshot of my metrics and matrices in the Report View:
Measure = IF(
MONTH( MAX( Calender[Start of Month] ) )=MAX(Calender[_Month] ) ,
MAX(Calender[_Month]), 0)
If the problem exists,please provide me with some sample data and the results based on the sample data. That would be very helpful. How to provide sample data in the Power BI Forum - Microsoft Fabric Community Or show them as screenshots or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.
Best Regards,
Sunshine Gu
Helper IHello ,
Thanks a lot, I did figure this out. Now I wanted to move ahead with my original task. I want to add forecasted sales (that will be predicted in each month for the next four months). Below snap shows how it looks in excel:
So I modified my measure as below:
exp v_BKsales1 = IF(
(MONTH(MAX(Calender[Start of Month]))+1)=MONTH(MAX(Calender[_Start_of_month])),
20,
IF( MONTH(MAX(Calender[Start of Month]))=MONTH(MAX(Calender[_Start_of_month])),
[UMSATZ_BK],
0)
)
Note that I have used 20 inplace of forecasting measure for simplicty sake.
As per the modified measure I should have been able to see 20 in each row next to sales. But it is not working:
It would be really helpful if you can help me sort this out.
Regards,
Varishtha N
Hi Varishtha ,
If you want to use 20 as your forecast sales value for the next month, please take a look at this test:
My Model View:
Calender = ADDCOLUMNS(CALENDAR(DATE(2024,1,1),DATE(2024,12,31)),"Start of Month",DATE(2024,MONTH([Date]),1),"_Month",MONTH([Date]))
I created another new table:
Table 2 = VALUES('Calender'[Start of Month])
My Report View and Measure value:
Measure = IF(
MONTH( MAX( 'Calender'[Start of Month] ) )=MAX('Calender'[_Month] ) ,
MAX('Calender'[_Month]), 0)
Measure 3 = IF( MONTH( MAX('Calender'[Start of Month])) = MONTH( MAX('Table 2'[Start of Month]) ) ,MONTH( MAX('Calender'[Start of Month])), IF( MONTH( MAX('Calender'[Start of Month])) +1 = MONTH( MAX('Table 2'[Start of Month]) ) , 20 , BLANK()))
Best Regards,
Sunshine Gu