SAMEPERIODLASTYEAR View

[bberglund]

I have a data that i need to show this year vs. last year. 

I found my issues for the contiguous data problem that throws the can't display the visual. 

I need assistance on setting the function up to not show or set a default value for the data that is not available. 

The example. 

My table has data for the dates needed however I am showing the information based on a category. 

August 1 - this year and last year.  Based on the categories

Cat 1    10.00    20.00

Cat 2    11.00    21.00

Cat 3      9.00      9.00

Cat 4     12.00     12.00 

Cat 5    10.00   1.00

When I span it over a few days, 

Aug 2 doesn't have data for Cat 4 for example. 

Aug 3 does. 

So i get the error cause the one total for that category is missing on one of the days in the range selected. 

What I want to do is set Cat 4 to 0 for that last year or this year value so that it does not error. 

If anyone can help, that would be great.  

[v-haibl-msft]

@bberglund

I am not sure what does your table like. If we have a table as below, we can create another crossjoin table to make the blank values shown as 0 by creating a calculated column.

Sales = 
VAR Temp =
    LOOKUPVALUE (
        Table1[Sales],
        Table1[Date], 'Table2'[Date],
        Table1[Category], 'Table2'[Category]
    )
RETURN
    IF ( Temp <> BLANK (), Temp, 0 )

Best Regards,
Herbert

[bberglund]

Herbert,

This is really good insight, however when I tried your LookupValue, I am getting an error. 

The table you described is really close.

My table is a little larger is all. 

I have to calculate the productprice as a sum for the sales. 

I have 

InvoiceDate

ProductPrice as a SUM

SubCategory. 

The SubCategory can be one of N categories. 

The SubCategory may have not been sold on the given InvoiceDate. 

That's when I get the SAMEPERIODLASTYEAR problem.  Is that when you select over too many dates and one of the categories does not exist, it will give the Can't display the visual error. 

So I need to set a category to 0 if that category doesn't exist in that date range.  Which I think you have in your example. 

I was able to create the SalesLookup Table as you have it described.  So that is there. 

The LookupValue statement then is throwing that error attached. 

[bberglund]

Found my mistake.  I had an extra ] in the formula.  
Took me a second to see that. 

Now that I have this lookup table to get the categories set to 0 value. 

How would you calculate those values against the same period last year. 

my formula right now is this: 

ProductPriceLastYear = (CALCULATE(SUM(TableauSalesView[ProductPrice]),SAMEPERIODLASTYEAR(TableauSalesView[InvoiceDate])))

I need to get the ProductPrice from last year that was set as a 0 from the lookup table. 

I wanted to try and put 

ProductPriceLastYear = (CALCULATE(SUM(TableauSalesView[Sales]),SAMEPERIODLASTYEAR(TableauSalesView[InvoiceDate])))

but that is not a field i can use. 

Thanks for your help Herbert. 

[v-haibl-msft]

@bberglund

If you do not have a calendar table, please create one and create relationship between the calendar table and the fact table.

In following screenshot, Table1 is the original fact table, Table2 is the crossjoin table.

Now you can create a measure with following DAX formula.

Measure =
CALCULATE ( SUM ( Table2[Sales] ), SAMEPERIODLASTYEAR ( 'Calendar'[Date] ) )

Best Regards,
Herbert

[bberglund]

Herbert 

I got your sample and have been trying to replicate the sample with my dataset. 

Is there a way I can share my file with you to take a look and help me get it to work?

Let me know how I can send you a file to review.

I am trying to get this finished before the end of the week if at all possible. 

Thanks

Much appreciated.

Brian

[bberglund]

This is the sample file look before i add the changes you had suggested. 

So I'd like to send this to you as a pbix and see if you can assist with the lower left chart for the last year data. 

[v-haibl-msft]

@bberglund

You can share the pbix file througn online file service like OneDrive.

Best Regards,
Herbert

[bberglund]

I sent you the file via private message. 

Can you let me know if you got it?

[v-haibl-msft]

@bberglund

Do you mean that the TableauSalesView is the crossjoin table which I mentioned before? And there is only one fact table now without Calendar table, and you've already made the blank values shown as 0, right? Currently all the visuals are displaying well in the canvas, could you please point out the problem now?

Best Regards,
Herbert

[bberglund]

In the sample I sent, I did not have the extra tables you sent. 

The dashboard works as is in small data ranges. 

When you select 8/6/17 - 8/12/17 for example, the lower left chart throws an error. 

I know the problem is that on one of the days last year for the LY column there as no sales for a particular category. 

I couldn't get the calendar and the cross join implemented correctly. 

Cot sure if you can give that a try in that sample and make it work? 

then i can review it... cause i am missing something, somewhere.

I'll send you the pbix file that i tried your methods on and you can see the error there as well.

Thanks