Show last Fiscal Year Spend to Date if Current Fiscal is Blank

[tek01]

I have below measure for Fiscal Year Spend to Date, this measure is to show current fiscal year and since I am in the new fiscal year it is showing blank() on my card visual.

Fiscal Year Spend to Date = 
Var LatestFY = Maxx(Filter('Financial Calendar', 'Financial Calendar'[Calendar Date]<=Today() ), [Financial Year])
Return Calculate([Spend], All('Financial Calendar'),'Financial Calendar'[Financial Year]=LatestFY)

How can i rewrite this measure to show last fiscal year if Current fisal year is blank?

[v-huijiey-msft · Accepted solution]

Hi @tek01 ,

Your requirement is to display the expenditures of the previous fiscal year when the current fiscal year data is unavailable or appears blank. Is my understanding correct?

Please try this measure:

Fiscal Year Spend to Date = 
VAR LatestFY = MAXX(FILTER('Financial Calendar', 'Financial Calendar'[Calendar Date] <= TODAY()), [Financial Year])
VAR CurrentFYSpend = CALCULATE([Spend], ALL('Financial Calendar'), 'Financial Calendar'[Financial Year] = LatestFY)
VAR PreviousFY = LatestFY - 1
VAR PreviousFYSpend = CALCULATE([Spend], ALL('Financial Calendar'), 'Financial Calendar'[Financial Year] = PreviousFY)
RETURN IF(ISBLANK(CurrentFYSpend), PreviousFYSpend, CurrentFYSpend)

I modified this measure is syntax based on what you provided.

During actual testing, I created the following sample data:

Created a calculated column to extract the year of the date column:

Year = YEAR('Financial Calendar'[Date])

Created a measure to calculate fiscal year expenditures:

Spend = SUM('Financial Calendar'[Sale])

Finally, a measure was created to meet the requirements:

Fiscal Year Spend to Date =
VAR LatestFY = MAXX(FILTER('Financial Calendar', 'Financial Calendar'[Date] <= TODAY()), [Year])
VAR CurrentFYSpend = CALCULATE([Spend], ALL('Financial Calendar'), 'Financial Calendar'[Year] = LatestFY)
VAR PreviousFY = LatestFY - 1
VAR PreviousFYSpend = CALCULATE([Spend], ALL('Financial Calendar'), 'Financial Calendar'[Year] = PreviousFY)
RETURN IF(ISBLANK(CurrentFYSpend), PreviousFYSpend, CurrentFYSpend)

The final visual effect is shown below:

pbix file is attached.

Please adjust according to your actual field information and measure name.

I would be grateful if you could provide me with sample data for testing, please remove any sensitive data in advance.

If you have any further questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!