Measure return blank

[Anonymous]

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 ?

[v-easonf-msft · Accepted solution]

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

[amitchandak]

@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