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...
Dashboards
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. 

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

        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