cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Apply different interval to PARALLELPERIOD using what-if parameters

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?

 

screenshot.png

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Frequent Visitor

Re: Apply different interval to PARALLELPERIOD using what-if parameters

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.

 

sreenshot4.png

View solution in original post

8 REPLIES 8
Highlighted
Super User V
Super User V

Re: Apply different interval to PARALLELPERIOD using what-if parameters

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)

Highlighted
Frequent Visitor

Re: 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.
screenshot2.png
 
Highlighted
Super User VI
Super User VI

Re: Apply different interval to PARALLELPERIOD using what-if parameters

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





Did I answer your question? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




Highlighted
Frequent Visitor

Re: Apply different interval to PARALLELPERIOD using what-if parameters

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.

screenshot3.png

Highlighted
Frequent Visitor

Re: Apply different interval to PARALLELPERIOD using what-if parameters

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.

 

sreenshot4.png

View solution in original post

Highlighted
Solution Specialist
Solution Specialist

Re: Apply different interval to PARALLELPERIOD using what-if parameters

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

offsetr2.jpg

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. 

Highlighted
Frequent Visitor

Re: Apply different interval to PARALLELPERIOD using what-if parameters

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.

Highlighted
Solution Specialist
Solution Specialist

Re: Apply different interval to PARALLELPERIOD using what-if parameters

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

offset.jpg

Link to the file is the same.

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

Kind regards, Steve. 

 

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.