Apply different interval to PARALLELPERIOD using what-if parameters

[Kazu]

I want to create a measure which refers to another measure value a few months before or later, depending on the value of what-if parameter. I would like to even apply different what-if parameter based on the value of a certain dimension.

I could apply a single what-if parameter using the DAX below. "Shifted Cost" measure refers to "Cost" measure in two months before, which is from "Offset A" what-if parameter. Now, I want to use different what-if parameter - "Offset A" for Project A and "Offset B" for Project B. How can I achieve that?

[Kazu · Accepted solution]

So I could not make IF() work, but I found a solution.

• Create flags in Project table to specify which Offset parameter to use.
• Multyply the flags in the measure formula

In this way, everything looks perfect as below. I am not sure if this is the best solution, though.

[stevedep]

@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. 

[mahoneypat]

You can add an IF() variable to get your desired result with a pattern like this:

Shifted Cost = var __projectoffset = SWITCH(SELECTEDVALUE(Main[Project]), "A", [Offset A], "B", [Offset B], BLANK())

Return (your measure with __projectoffset in place of [Offset A Value] measure)

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[Kazu]

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.

[lbendlin]

Encapsulate the Offset part in an If statement (in case you only have projects A and B) or add another measure that combines the project and whatif filters (for more flexibility with more than two projects)

[stevedep]

@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. 

[Kazu]

Thanks @stevedep  and sorry for the late response.

It seems your formula works for the total in time dimension, but not for other dimension (e.g. total of A+B would be wrong). That said, it was good to know that it is not for me that this is difficult.

[Kazu]

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.