PARRALEL Period Creating a new row instead of adding to the existing row

[DebbieE]

Updated because I have done more testing.....

I have dim Date, Dim Product, Dim Area

Dim Date

DateKey      dayMonth

01062023   June 2023

01072023   June 2023

DimProduct

ProductKey  Product  Owner     date (meta Data)

1                   Prod 1   J Jones    June 2023

2                   Prod 1   L Paul      July 2023

DimArea

ProductKey  Product  Area Owner     date (meta Data)

1                   Area 1  John Smith       June 2023

2                   Area 1  Kim Pool           July 2023

And a Fact table

DateKey       ProductKey AreaKey Score

01062023   1                   1             4

01072023   2                   2              5

this is an example of data brought together from dims and the score from the fact.

Date    Product  Area       Area Owner                Count Score

Jun 23   Prod 1    Area 1     John Smith                 1          4

Jul 23    Prod 1    Area 1     Kim Pool                     1         5

I created DAX for trends

So I have a base measure for the average score

And then I create this DAX

LM Score = CALCULATE([Avg Score],PARALLELPERIOD('Dim Date'[Date],-1 Month))

And I when I slice on Jul 2023 I get this in Power BI

                                              Area Owner           Count Score   LM Score

Jul 23   Prod 1    Area 1         Kim Pool                 1           5         

Jul 23   Prod 1    Area 1       John Smith                                    4

Its like its creating a new row as soon as you add in LM Score and adding the Last month score to this new row. 

What is confusing to me is that we see Junes Area Owner against July 2023 from Dim Area. I cannot understand why this is happening and how I can stop it from happening

Can any one give me any pointers on how to resolve this?

[rrm121812]

Hi DebbieE,

The issue is with the context filter which is being applied on Area Owner since the value of Area owner is different for both the months. This would happen for any column which is included in the Table visual and has different values in different Months.

The way to correct this is to remove the context filter from the last month calculation as below:

LM Score = CALCULATE([Avg Score],PARALLELPERIOD('Dim Date'[Date],-1 Month),REMOVEFILTERS('TAbleName'))

OR

LM Score = CALCULATE([Avg Score],PARALLELPERIOD('Dim Date'[Date],-1 Month), ALL('TAbleNAme'))

If you find this helpful, Please provide Kudos and accept this as a solution.

[DebbieE]

It just doesnt make sense to me because the old Area row is showing against July and the fact table doesnt have this.

I tried the first DAX and now Im geting some kind of cross join with absolutely every possibility appearing in the table visual so thats not worked.

I tried the second DAX which has dont exactly the same thing and now I have every single combination of dim so this hasnt worked either.

I unhid the Area Key in the fact table and added that so both of the above do this

                           Area Key                   Area Owner                            Count Score   LM Score

Jul 23   Prod 1     1                               Area 1         Kim Pool                 1           5         

Jul 23   Prod 1     2                                Area 1       John Smith                                     4

Jul 23   Prod 1      3                              Area 1       John Smith                                     4

Jul 23   Prod 1     4                                Area 1       John Smith                                     4

Jul 23   Prod 1     5                                Area 1       John Smith                                     4

Jul 23   Prod 1      6                               Area 1       John Smith                                     4

Jul 23   Prod 1      7                               Area 1       John Smith                                     4

Jul 23   Prod 1       8                              Area 1       John Smith                                     4

etc so its now just not joining to the dims. Its really confusing. Im not understanding this at all