SAMEPERIODLASTYEAR encapsulated into a SUMX/SUMMARIZE

[Maikeru]

Dear all

I am trying to implement a volume effect measure which essentially take as an input Volume and Sales of LastPeriod and Current Period 

Volume effect (unit) = 
IF([Volume LastPeriod]=0, 
        // Exception case if  [Volume LastPeriod] = 0 then Volume effect = (Sales CurrentPeriod - Sales LastPeriod)
        [Amount CurrentPeriod] - [Amount LastPeriod], 
        // General case if [Volume LastPeriod] <> 0 then Volume effect = ( Volume CurrentPeriod - Volume LastPeriod ) * Price LastPeriod
        ([Volume CurrentPeriod] - [Volume LastPeriod]) * DIVIDE([Amount LastPeriod], [Volume LastPeriod],0))

Now the complex part is that the formula need to be applied at a specific granularity level in the dataset (for example by country/customer/product) and then ensure the effect at the upper level is equal to the sum of the volume effect of the lower level.

I have implemented this idea by encapsulating the above measure into a SUMX and SUMMARIZE function as below:

Volume effect (SUMX) = 
SUMX(
    SUMMARIZE(Sales, Sales[Country], Sales[Customer], Sales[Material]),
    [Volume effect (unit)]
)

The measure works well except in one situation when Volume CurrentPeriod is blank.

I suspect this has to do with the SUMX/SUMMARIZE function messing up with the SAMEPERIODLASTYEAR since Volume effect (unit) computes properly in all cases.

Here the link to the pbix.

Any help/insights would be much appreciated.

Best regards

Michael 

[Greg_Deckler]

@Maikeru - Personally, I would ditch time intelligence functions and just do it directly so you can control and troubleshoot what is going on. 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...

[Maikeru]

Dear @Greg_Deckler 

Thank you for sharing. It's a very interesting method.

I tried to implement your idea to my 2 LastPeriod measures as below, using dates instead of splitting Year/month:

Volume LastPeriod (TITHW) = 
VAR __MaxDate = MAX('Calendar'[Date])
VAR __MinDate = MIN('Calendar'[Date])
VAR __TmpTable = CALCULATETABLE('Sales',ALL('Calendar'[Date]))
VAR __MaxDate_PY = DATE( YEAR(__MaxDate)-1, MONTH(__MaxDate), DAY(__MaxDate))
RETURN 
SUMX( 
    FILTER(__TmpTable, 
        [Date] >= DATE( YEAR(__MinDate)-1, MONTH(__MinDate), DAY(__MinDate)) &&
        [Date] <= DATE( YEAR(__MaxDate)-1, MONTH(__MaxDate), DAY(__MaxDate))
    ),
    [Volume]
)

Amount LastPeriod (TITHW) = 
VAR __MaxDate = MAX('Calendar'[Date])
VAR __MinDate = MIN('Calendar'[Date])
VAR __TmpTable = CALCULATETABLE('Sales',ALL('Calendar'[Date]))
VAR __MaxDate_PY = DATE( YEAR(__MaxDate)-1, MONTH(__MaxDate), DAY(__MaxDate))
RETURN 
SUMX( 
    FILTER(__TmpTable, 
        [Date] >= DATE( YEAR(__MinDate)-1, MONTH(__MinDate), DAY(__MinDate)) &&
        [Date] <= DATE( YEAR(__MaxDate)-1, MONTH(__MaxDate), DAY(__MaxDate))
    ),
    [Amount]
)

The measures appear to be working fine.

However when I encapsulate those 2 new measures in the SUMX/SUMMARIZE I get the same issue as when using standard time intelligence.

As shown on the below screenshot, the yellow cells are blank when I would expect the value from the orange cells to be carried over. This happens only in the case the dimensions do not exist in selected CurrentPeriod (red cells).

Come to think about it there is some logic to it, since I suppose SUMX can iterate only on values/dimension which exist in the dataset.

Just wonder if there is some clever way to work around this. 

Here is the latest pbix for reference.

Any insights/ideas would be much appreciated.

Best regards

Michael

[Greg_Deckler]

@Maikeru - Hmm, I'll have to take a look. I'm working on a project that is intended to destroy the need for time "intelligence" functions once and for all. Solely for the reason that I find them unintuitive, overly complicated and stupid. So let me take a closer look at your PBIX file.

[Maikeru]

Very much appreciated @Greg_Deckler! This sounds promising!

I will also continue looking for a workaround and post the updates on this thread.

[Maikeru · Accepted solution]

@Greg_Deckler 

I think I got something. I created a standalone Date1 table and recalculated my measures based on the selected values.

Volume 1 = 
VAR __MinDate = MIN(Date1[Date])
VAR __MaxDate = MAX(Date1[Date])
RETURN 
CALCULATE(
    SUM(Sales[Volume]),
    FILTER(Sales, Sales[Date] >= __MinDate && Sales[Date] <= __MaxDate)
)

Volume 1 LY = 
VAR __MinDate = MIN(Date1[Date])
VAR __MaxDate = MAX(Date1[Date])
RETURN 
CALCULATE(
    SUM(Sales[Volume]),
    FILTER(Sales, 
        Sales[Date] >= DATE( YEAR(__MinDate)-1, MONTH(__MinDate), DAY(__MinDate)) &&
        Sales[Date] <= DATE( YEAR(__MaxDate)-1, MONTH(__MaxDate), DAY(__MaxDate))
    )
)

This seems to solve the issue.