DAX measure to pull last year's values when none exist for this year

[esuing]

This seems like it should be a common issue, but I couldn't find anything on it. I'm thinking it should be simple.  I have a date slicer on a calendar table for month.  I'm showing that month's current sales and I'd like to show last year's same month sales as well. 

Everything looks fine, except the customer would like to see a Ship To listed if (in this case) we had Lbs in the Prev Yr Invcd Lbs field and no data in the June 2019 "Total Final Invoice Lbs" field.  Seems the slicer is eliminating those. Here's my measures I'm using so far:

Total Final Invoice Lbs = SUM('Invoice Lbs'[Invoiced Lbs])

Prev Yr Invcd Lbs = CALCULATE([Total Final Invoice Lbs],SAMEPERIODLASTYEAR('Period Start Date Calendar'[Period Start Date]))

Any help would be greatly appreciated. I'm hoping once I get this figured out, I'll be able to do the same for YTD columns that they'll want to see added as well.

Thanks!

Eric

[Ashish_Mathur]

Hi,

Try this measure

=IF(ISBLANK(SUM('Invoice Lbs'[Invoiced Lbs])),0,SUM('Invoice Lbs'[Invoiced Lbs]))

Hope this helps.

[esuing]

Ok, this brings in the customers, however, my [Prev Yr Invcd Lbs] still shows 0 for when there should be lbs. 

For example, in the screenshot, I'm filtering for June 2019 with the slicer. For the Valley Crest Foods row, the sameperiodlastyear() function is not returning the June 2018 lbs. I added a measure using the date hardcoded to demonstrate that there ARE lbs in June 2018 it should display.  The last column is using the ALL() function around my dates and it does bring in all lbs... but that's not really what we're looking for and I was just trying to peice it together... I just can't figure out why the sameperiodlastyear() function isn't working or how to get a workaround. I expecting it to return the same value as my hardcoded measure.

Here are my current measures:

June 2019 = IF(ISBLANK(SUM('Invoice Lbs'[Invoiced Lbs])),0,SUM('Invoice Lbs'[Invoiced Lbs]))

sameperiodlastyear(dates) = CALCULATE([Total Final Invoice Lbs],SAMEPERIODLASTYEAR('Invoice Headers'[Period Start Date]))

hardcoded for june 2018 = CALCULATE([Total Final Invoice Lbs],'Invoice Headers'[Period Start Date]=DATE(2018,6,1))

Hope I'm explaining this clearly enough.

Thanks.

Eric

[Ashish_Mathur]

Hi,

One reason could be that you do not have a Calendar Table.  There should be a Calendar Table with a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  The slicer should be built from the Calendar Table.  I can offer further help, if you share the link from where i can download your PBI file.  In that file, clearly indicate the answer that you are expecting.