SAMEPERIODLASTYEAR

[lxmnl]

Hi there,

 

I'm using the formula below to get last year's value:

Yield-1Y = CALCULATE(SUM('US Treasury'[Yield]),DATEADD('US Treasury'[Date],-1,YEAR))
or
Yield-1Y = CALCULATE(SUM('US Treasury'[Yield]),SAMEPERIODLASTYEAR('US Treasury'[Date]))

How can I ask it to get the latest available data before last year if last year appears to have blank data eg its a holiday or weekend?

[v-angzheng-msft]

Hi, @lxmnl 

Getting the data for the same period last year is not difficult to implement, but if additional judgments are needed for working days, more complex logic may be required.

You can try using the LASTNONBLANKVALUE  function to get help.

https://community.powerbi.com/t5/Desktop/LASTNONBLANKVALUE-challenging-problem/m-p/1749034

Or consider using other similar time functions, such as Dateadd, EDATE and other functions

https://databear.com/power-bi-dax-sameperiodlastyear-paralellperiod-and-dateadd/

Best Regards,
Community Support Team _ Zeon Zheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@lxmnl , first you should date table with time intelligence

Do you want to work with only working days?

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Check this example

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

[lxmnl]

yes only working days @amitchandak