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

    • bberglund's avatar
      bberglund
      Frequent Visitor
      8 years ago

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

        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.