Determining the previous month value of a dimensional attribute

[RobinVanBeijnum]

Hi all, 

We are struggling a bit with getting the previous month value of a dimensional attribute. We have the following table setup (see picture at the end, we created a small POC set and are playing around a bit, hence the bidirectional filtering):

Dim Date (sample, defined as date dimension in Power BI):

PK_Date	D_Date (date field, defined as date column for date dimension)
20240131	2024-01-31
20240201	2024-02-01
20240202	2024-02-02
...	...

Dim Counterparty (sample):

PK_Counterparty	Name
-1	Unknown
1	Abc
2	Def
...	...

Dim Counterparty RC (sample):

PK_Counterparty_RC	Score
-1	Unknown
1	A
2	B
3	C
...	...

Fact (sample):

FK_Date	FK_Counterparty	FK_Counterparty_RC	Other FKs...	Amount
20240131	1	3	...	10
20240229	1	2	...	10
20240331	1	3	...	10
...	...	...	...	...

We are trying to detemine the Score (table: Dim Counterparty RC, CRG in the screenshot) of the previous month and report it on one line next to the current month score. Our fact contains monthly data, using the last day of the month as key. The fact will eventually contain more keys (the grain is product, lower than counterparty and month), we made it smaller for the POC. The score we are trying to determine is on the grain counterparty (a counterparty has one score that can change over time).  

Any help is much appreciated! 

[sjoerdvn · Accepted solution]

Gross Carrying Amount (Counterparties prev Month) = 
IF(HASONEVALUE('dwh D_Date'[MM-YYYY]) && HASONEVALUE('dwh D_Counterparty_Risk_Classification'[Crg]),
    -- get all distinct counterparties from the fact table
    VAR cp_pm = CALCULATETABLE(VALUES('dwh F_CP_Test'[FK_D_Counterparty])
        -- replace date context with previous month
        , PREVIOUSMONTH('dwh D_Date'[D_Date])
        --replace active default Crg context with the Crg context from the helper dimension
        , TREATAS(VALUES(D_Risk_Classification_Helper[Crg]),'dwh D_Counterparty_Risk_Classification'[Crg]))
    -- sum the gross carrying amount for the current month context, current crg context, filter for counterparties that had the helper crg last month
    RETURN CALCULATE(SUM('dwh F_CP_Test'[Gross_Carrying_Amount]),cp_pm)
)

I assume this is what you're looking for. Also added some comments in there.
I do not know the data you are working on, but I can imagine adding an extra value in that helper dimension to show values for counterparties that maybe did not yet exist in the previous month. 
That would require some extra logic in those measures. 

[RobinVanBeijnum]

Thanks! This does the trick indeed.

[sjoerdvn]

To be honest I don't really understand the model and what you are trying to achieve here; but maybe you created a computed column where you should have created a measure. Rember that computed columns are calculated at load time, so do not take into account context like slicers or page filters.

[RobinVanBeijnum]

The model is built this way due to the amount of data and the different granularity of attributes. We have (in the simplefied POC) counterparties (customers) and products. Each row in the fact represents a end of month snapshot of the customer / product.

Customers get certain ratings that change over time. Since these ratings are not numerical and you don't calculate with them, we added them to a separate dimension (we chose on purpose not to model them as SCD type 2 in the dimension counterparty). So our fact table now contains a key (ISO date as integer) for the end of month date of the reporting period, a key for the customer, a key for the product and a key the customer rating dimension.

What we are trying to achieve is to create a migration matrix; rating this period on one axis of a matrix, rating previous period on another axis of a matrix and then any amount as measure. In this visualisation you can see movements from one rating to another compared to the previous month. 

	Rating previous month: A	Rating previous month: B	Rating previous month: C
Rating this month: A	500 (so 500 was in A, stayed in A)	100 (so 100 was in B, moved to A)	50 (so 50 was in C, moved to A)
Rating this month: B	125 (so 125 was in A, moved to B)	75 (and so on...)	75 (and so on...)
Rating this month: C	75 (and so on...)	75 (and so on...)	75 (and so on...)

We tried both calculated columns and measures, but we couldn't get either one working, alas...

[sjoerdvn]

created these measures:

# CounterParties = DISTINCTCOUNT('dwh F_CP_Test'[FK_D_Counterparty])

#Counterparties prev Month = 
IF(HASONEVALUE('dwh D_Date'[MM-YYYY]) && HASONEVALUE('dwh D_Counterparty_Risk_Classification'[Crg]) && HASONEVALUE(D_Risk_Classification_Helper[Crg]),
    VAR cp_cm = VALUES('dwh F_CP_Test'[FK_D_Counterparty])
    VAR cp_pm = CALCULATETABLE(VALUES('dwh F_CP_Test'[FK_D_Counterparty])
        , PREVIOUSMONTH('dwh D_Date'[D_Date])
        , TREATAS(VALUES(D_Risk_Classification_Helper[Crg]),'dwh D_Counterparty_Risk_Classification'[Crg]))
    RETURN COUNTROWS(INTERSECT(cp_cm, cp_pm))
)

[RobinVanBeijnum]

Hi Sjoerdvn,

Thanks a lot for all the help! We are able to reproduce the results for number of counterparties, even though I do not fully understand what happens... 

How can we use this solution to report an amount instead of number of clients? We would like to show SUM('dwh F_CP_Test'[Gross_Carrying_Amount]) as measure in the migration matrix.

[sjoerdvn]

OK, now I understand what you're trying to do. Good news is that you can perfectly achieve this, but the solution is not all that obvious, especially if you are new to Power BI.
Since you have the rating on mutiple axis, I suggest you create a new (helper) dimension for that rating. That could be done as simple as creating a computed table based on the unique columns in that table, example below.

D_Risk_Classification_Helper = VALUES('dwh D_Counterparty_Risk_Classification'[Crg])

The Next step would be to create a measure. That one is a bit more complex but I can give some examples on that later, hopefully today if I can find the time.

[lbendlin]

What made you choose bidirectional relationships?  Those should be reserved for special cases.

Read about fact and dimension concepts. Fact tables are controlled by dimension tables. Reports are filtered on dimensions.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[RobinVanBeijnum]

Hi Ibendlin and other forum visitors,

We set the filter direction to both ways because we were trying around, as explained above. We are aware how fact and dimension concepts work.

We created a POC Power BI: POC Dim.pbix 

This POC contains a small sample set of data. We also added two additional dimensions, which are there only to showcase the difference in grain. Counterparty with id = 1 has three different products.

The file contains a visual and a failed attempt to realise the previous month value of (in this case) the CRG rating on columns of the matrix. We are aiming at visualising the migration between scores (this month CRG on Rows, previous month CRG on Columns and a measure on Values).