Forum Discussion

akhoury's avatar
akhoury
Icon for Helper I rankHelper I
6 years ago
Solved

SUMMARIZE function and SAMEPERIODLASTYEAR returning BLANK

Dear PBI community,

 

Im having an issue with the Summarize function, especially when applying SAMEPERIODLASTYEAR.

 

The model I have is:

-oos_sales Table which has sales transactions by product, customer and day

-Calendar Table, which is the main date table connected to the above oos_sales date column

 

The PBI page has several slicers, the one worth mentionning is the year, typically selected to current year (2020).

 I need to run a more complex calculation than the below pasted code, but I simplified it so to better explain what the real issue is:

 

In short - im trying to SUMMARIZE the sales transaction table to sales by item, and for each item, the SUMX of the difference between YTD and previous year volume. Below is the code

Volume (L) at risk = 
VAR SKU_groupedby =
    SUMMARIZE (
        oos_sales,
        oos_sales[item],
        "Current_sales", SUM ( oos_sales[sales - volume (L)] ),
        "prev_year_sales",
            CALCULATE (
                SUM ( oos_sales[sales - volume (L)] ),
                SAMEPERIODLASTYEAR ( 'Calendar'[Date] )
            )
    )
RETURN
    SUMX ( ( SKU_groupedby ), [Current_sales] - [prev_year_sales] )

 

The problem: Prev_year_sales seems to always be blank. Im guessing the table passed to the SUMMARIZE function does not have previous years records as I have the Year slicer set to 2020 (current year). 

The solution I tried was to apply the ALLSELECTED filter to the oos_Sales table Im passing to the SUMMARIZE function, but this is distorting the figures by summing all selected item's sales at each item row in the summarize table.

 

I understand that the Summarize function has a particular behavior, and have spent hours trying to understand it with no luck.

 

Can anyone show me the way? 

 

Appreciate your help. Thank you in advance

 

Aminek

 

  • HI, I see what you are trying to achieve. I would recommend to use addcolumns along with summarize, for additional columns, i.e., sales & previous year sales. 

    So the syntax should be Addcolumns ( Summarize ( Sales , Sales [Item] ), "Current Year Sales" , [Current Year Sales Measure] , "Prev Year Sales" , [Prev Year Sales Measure] )

3 Replies