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

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

SAMEPERIODLASTYEAR View_2.pbix125 KB
  • 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. 

       

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

        bberglund

         

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

         

        Best Regards,
        Herbert