Forum Discussion

tonk's avatar
tonk
Frequent Visitor
2 years ago
Solved

Cannot display previous year sales

I have the following table structure.

 

 

And I have the following DAX measures.

 

1. Actual Sales = SUM(Sales[ACTSALES])

2. Budget Sales = SUM(Sales[BGTAMT])

3. Previous Year Sales = CALCULATE(

    SUM(Sales[ACTSALES]),
    SAMEPERIODLASTYEAR(Sales[SalesDate])
)
 
However, my "Previous Year Sales" Column is empty when I display it in my table view.  If I don't select any month, total figures can be displayed in matrix.  If I select any months, "Previous Year Sales" is empty in matrix.  Do you know how to solve it?
 

 

 

 

Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    2 years ago

    Hi,

    Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  Drag Year, Month name to any visual only from the Calendar Table.  Write this measure

    Previous Year Sales = CALCULATE([Actual sales],SAMEPERIODLASTYEAR(Calendar[Date]))

    Hope this helps.

1 Reply

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by the Month number.  Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  Drag Year, Month name to any visual only from the Calendar Table.  Write this measure

    Previous Year Sales = CALCULATE([Actual sales],SAMEPERIODLASTYEAR(Calendar[Date]))

    Hope this helps.