How to calculate sameperiodlastyear and leaving out blank dates

[Anonymous]

Hi,

I want to calculate SPLY and have made a measure that does that but the problem is when I make a KPI with SPLY based on a filter (Normal Day) it fails as some dates are blank. Is it possible to only calculate SPLY for the dates where there are values in both years AND/OR make all the blank values = 0?

For instance, looking at the example below the KPI will only be based on 23/5 and 25/5-3/6.

Thank you! 🙂

[v-luwang-msft · Accepted solution]

Hi @Anonymous ，

What dax you ued ？

You can try using another formula instead SAMEPERIODLASTYEAR.

• This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

And similar qestion refer the following link，and also some user   changed the filter direction from both to single their problem was solved,you could have a try:

https://community.powerbi.com/t5/Desktop/Function-SAMEPERIODLASTYEAR-expects-a-contiguous-selection-Issue/td-p/583072/page/2 

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[johnt75]

can you share your PBIX file ?

[Anonymous]

Unfortunately no as I am running DirectQuery to a Snowflake DB. But it is very simple. Two tables: A date table and a Fact Table where I have connected the Date Table to the Fact Table on a date key. And as you can see in the pictures I have date (from date table), Tickets sold (from Fact Table) and Type (from date table).
The type in the date table represents what type of day it is (normal day, holiday, etc.). There are some dates in the Fact Table where there are no data because the ticket store was closed or they just didn't have any records of tickets sold - which is why there are blank dates.

[johnt75]

You can use COALESCE to return 0 instead of blank. Just wrap your current measure definition in COALESCE( Measure definition, 0 ).

[Anonymous]

I still get an error unfortunately, when filtering on "Normal Day". If I choose another type of day like "Industri-ferie" there is no problem even though there's gaps:

Normal Day:

Industri-ferie:

[johnt75]

Do you have a Date table? If not, I'd create one, link it to your data table and then use the date column from your Date table as the argument to SAMEPERIODLASTYEAR.

DAX Time Intelligence functions tend to need a proper Date table to work correctly.

[Anonymous]

I have already done that. 🙂