Forum Discussion

iluvcoding_91's avatar
4 years ago
Solved

Same period last year calculation (include rows where current year sales is $0)

Hello, I have a FACT table where I'm trying to sum up the current year sales and prior year sales (side by side in a matrix chart) using the sameperiodlastyear DAX function. I want the matrix to show ALL records from both years (i.e., if sales for a particular country were generated last year, but not in the current year, I want those records displayed as well). The problem is the sameperiodlastyear function will only retrieve records from the prior year ONLY IF current year sales exists. Otherwise, all prior years records that don't exist in the current year will be filtered out. Any idea on how I can modify the measures to include all sales from both years?

 

  • Thanks, it was my data model that was not producing the correct result

3 Replies

  • Hi,

    I am not sure if I understood correctly about your data model., but please check the below picture and the attached pbix file.

     

     

    Sales total sameperiodlastyear: =
    CALCULATE (
        [Sales total current year:],
        SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
    )
    
    • iluvcoding_91's avatar
      iluvcoding_91
      Icon for Helper I rankHelper I

      Thanks, it was my data model that was not producing the correct result

      • v-easonf-msft's avatar
        v-easonf-msft
        Icon for Community Support rankCommunity Support

        Hi, iluvcoding_91 

        If the issue is resolved, you can close this thread by marking the relevant reply as solution.

        Best Regards,
        Community Support Team _ Eason