Forum Discussion

bberglund's avatar
bberglund
Frequent Visitor
8 years ago

SAMEPERIODLASTYEAR View

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.  

 

Dashboards

10 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    8 years ago

    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's avatar
      bberglund
      Frequent Visitor
      8 years ago

      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's avatar
        bberglund
        Frequent Visitor
        8 years ago

        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.