Apply different interval to PARALLELPERIOD using what-if parameters

Thank you lbendlin 

I am not sure I understand your suggestions correctly, but I tried below, which acutally I knew should not work. I am new to DAX and still struggling to get away from tabluar way of thinking, but I think the reason why neither below works is because the selection of what-if parameter is done after the aggregation by SUMX. Instead, I want parameter selection to be done before SUMX.

I first tried IF statement like:

Shifted Cost = CALCULATE(SUMX('Main', 'Main'[Cost]), PARALLELPERIOD(Dates[Date], IF(Main[Project]="A",'Offset A'[Offset A Value],'Offset B'[Offset B Value]),MONTH))

 

It shows this error. I think I understand what this means.

 

A single value for column 'Project' in table 'Main' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

 

Next, I added SELECTEDVALUE, although I knew it does not return what I want.

Shifted Cost = CALCULATE(SUMX('Main', 'Main'[Cost]), PARALLELPERIOD(Dates[Date], IF(SELECTEDVALUE(Main[Project])="A",'Offset A'[Offset A Value],'Offset B'[Offset B Value]),MONTH))

 

This gave me the results below, which is very interesting but obviously not what I want.

 

Kazu ; I see your challenge, I got it working with some trickery, not the most elegant solution, but is working with a measure for month level and one to aggregate:

FinalOffSetValue = 
IF(ISFILTERED('Date'[Date].[Month]);[SalesWithOffsetDayLevel];[SalesWithOffsetMonthOrAbove])

SalesWithOffsetMonthOrAbove = 
SUMX(
    VALUES('Table'[Date]);
    var _date = SELECTEDVALUE('Table'[Date])
    var _Category = SELECTEDVALUE('Table'[Category];"Multiple")
    var _CT = 
            CALCULATETABLE(
                SUMMARIZE('Table';'Table'[Date];"OffsetSales";CALCULATE(SUM('Table'[Sales])));
                    PARALLELPERIOD('Table'[Date];
                            SWITCH(_Category;"A";'Offset A'[Offset A Value];
                            "B";'Offset B'[Offset B Value])    
                            ;MONTH))
    return    
    SUMX(_CT;[OffsetSales])    
    )

SalesWithOffsetDayLevel = 
CALCULATE(SUM('Table'[Sales]);
         PARALLELPERIOD('Date'[Date];
                SWITCH(SELECTEDVALUE('Table'[Category]);"A";'Offset A'[Offset A Value];
                "B";'Offset B'[Offset B Value])    
                ;MONTH))

Working as seen here:

So FinalOffSetValue is displaying the right value on all levels. 

Power BI file is here. 

Does it solve it? Please mark as solution. Does it help? Thumbs up would be great.

p.s. where the labels say, day level, I mean month level. 

Kind regards, 

Steve. 

Thank you mahoneypat ,

Here is what I tried. Two issues.

1. The value cannot be shifted to the intersection which does not exist before shifting. A little hard to explain for me... the Project A -June and Project B Jan below.

2. Total is totally wrong. I think this is because the shifting by parallelperiod happens after the aggregation by SUMX, but don't know how I can shift and then aggregate.

Kazu , I gave it another try and indeed the difficulty is to determine the offset value for the parr period when the filter context is on an aggregate level, for this we need to know the category on a lower level, I first tried something like this again:

SalesWithOffsetWrong = 
VAR _OffsetA = 'Offset A'[Offset A Value]
VAR _OffsetB = 'Offset B'[Offset B Value]
RETURN
    CONCATENATEX(    
           CALCULATETABLE(
            SUMMARIZE (
                'Table';
                'Table'[Category];
                'Date'[Date];
                "OffsetSales"; CALCULATE ( SUM ( 'Table'[Sales] ) )
            );
            PARALLELPERIOD ( // this is a context filter, category is not known because its not a row level, on higher aggregation level this gives issues. 
                'Date'[Date];IF(SELECTEDVALUE('Table'[Category])="A";_OffsetA;_OffsetB);
                MONTH
            ));      
        [OffsetSales]
    )

But that won't work because the category is not known in the aggregate level. So I figured something needs to be done on row level, like is done with a filter. 

Therefore, I ended up with the following working code:

SalesWithOffsetRight =
VAR _OffsetA = 'Offset A'[Offset A Value]
VAR _OffsetB = 'Offset B'[Offset B Value]
RETURN
    CALCULATE (
        SUMX (
            FILTER ( 'Table'; 'Table'[Category] = "A" );
            CALCULATE ( SUM ( 'Table'[Sales] ) )
        );
        PARALLELPERIOD ( 'Date'[Date]; _OffsetA; MONTH )
    )
        + CALCULATE (
            SUMX (
                FILTER ( 'Table'; 'Table'[Category] = "B" );
                CALCULATE ( SUM ( 'Table'[Sales] ) )
            );
            PARALLELPERIOD ( 'Date'[Date]; _OffsetB; MONTH )
        )

This gives the right results without the use of a helper table.

Link to the file is the same.

Perhaps this answers your original question? If so, pls mark as such.

Kind regards, Steve.