SUMMARIZE function and SAMEPERIODLASTYEAR returning BLANK

[akhoury]

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

[sandeepsingh · Accepted solution]

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

[Greg_Deckler]

@akhoury - I haven't really see time "intelligence" functions work well inside something like SUMMARIZE. You might try using GROUPBY instead of SUMMARIZE if you can but probably won't work there either. 

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

If you can post sample data and expected output, can work to see if there is a solution.

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[akhoury]

@Greg_Deckler Spot on regarding time intelligence. Your links highlighted important aspects about it I did not know.

Thanks for sharing and your comments on how to present our issue.

Aminek