SAMEPERIODLASTYEAR Exclude where no data

[stribor45]

I use this function to show the data for the current date selected and the same time a year ago in my table but sometimes I don't have data for the previous period and I wonder whether I can exclude that. As you see in this image I don't have data for Sept 2021 so I would like to exclude that row from the table.

[bhanu_gautam · Accepted solution]

@stribor45 , Try using below method

HasDataLastYear =
VAR PreviousYear = CALCULATE(MAX('YourTable'[Date]), SAMEPERIODLASTYEAR('YourTable'[Date]))
RETURN
IF(
NOT(ISBLANK(CALCULATE(SUM('YourTable'[YourMeasure]), 'YourTable'[Date] = PreviousYear))),
1,
0
)

Please accept as solution and give kudos if it helps

[Anonymous · Accepted solution]

Hi @stribor45 ,

Hope all is going well.

To achieve your needs please follow these steps:

1. Create a measure that checks if the previous year's data is empty. This is done to filter out rows that have no data for the previous year.

measure = ISBLANK([Table])

2. After selecting the visual object, in the "Filter" pane on the right, apply measure as a filter to the current visual object. Set the filter condition to display data when the value of "measure" is FALSE.

If you want to learn more, please refer to the following documents:

ISBLANK function (DAX) - DAX | Microsoft Learn

If you have any questions or need help, 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!

[bhanu_gautam · Accepted solution]

@stribor45 , Try using below method

HasDataLastYear =
VAR PreviousYear = CALCULATE(MAX('YourTable'[Date]), SAMEPERIODLASTYEAR('YourTable'[Date]))
RETURN
IF(
NOT(ISBLANK(CALCULATE(SUM('YourTable'[YourMeasure]), 'YourTable'[Date] = PreviousYear))),
1,
0
)

Please accept as solution and give kudos if it helps