Forum Discussion
Measure return blank
Hi guys,
I have two selectors, fiscal date and month. Based on this selection I want to return the value for the previous fiscal year and month.
I have two tabels: the fiscal year comes from the fact table and the month comes form the dimension table
So the problem arises when I select current fiscal year 21/22 and month April onward, the measure returns BLANK values. The measure is supposed to return sum value of the previous fiscal year 20/21 and its month.
When I select 21/22 and April the measure should return value of 5.
I also deactivated the realationship from month to Fiscal selector so I have the choice to sleect previous years values.
I am sharing my powerbi file for more clarification:
https://drive.google.com/file/d/1z43jg5ucy02iqJlJIQFJIrGHoJ9bUtjJ/view?usp=sharing
Does anybody know how to fix this ?
Hi, Anonymous
You just need to add a new fiscal table：
Fisical Table = VALUES('Test table'[Fiscal])Then replace 'Test table'[Fiscal]' with ’Fisical Table[Fiscak]‘ in VAR _currentFiscal.
Sum value = VAR _currentFiscal = MAX ( 'Fisical Table'[Fiscal] ) VAR _previousFiscal = ( LEFT ( _currentFiscal, 2 ) - 1 ) & "/" & ( RIGHT ( _currentFiscal, 2 ) - 1 ) RETURN CALCULATE ( SUM ( 'Test table'[Value] ), 'Test table'[Fiscal] = _previousFiscal )Best Regards,
Community Support Team _ Eason
2 Replies
Anonymous , You only have prior year data for 1,2,3 month. And the prior year formula is working correct.
I done some correction to model, do do same in better manner
Hi, Anonymous
You just need to add a new fiscal table：
Fisical Table = VALUES('Test table'[Fiscal])Then replace 'Test table'[Fiscal]' with ’Fisical Table[Fiscak]‘ in VAR _currentFiscal.
Sum value = VAR _currentFiscal = MAX ( 'Fisical Table'[Fiscal] ) VAR _previousFiscal = ( LEFT ( _currentFiscal, 2 ) - 1 ) & "/" & ( RIGHT ( _currentFiscal, 2 ) - 1 ) RETURN CALCULATE ( SUM ( 'Test table'[Value] ), 'Test table'[Fiscal] = _previousFiscal )Best Regards,
Community Support Team _ Eason
