Period Over Period Change Help

[matthew_hampton]

Good Evening - 

I am having some visualization issues when wanting to display a month over month change without a date on my visualization.

This is what the data SHOULD look like:

accountnumber	Period	ARPU	ARPU Prior
5131000000000	-1	$  118.99
5131000000000	0	$  197.44	$     118.99
5131000000000	1	$  201.32	$     197.44
5131000000000	2	$  170.12	$     201.32
5131000000000	3	$  141.75	$     170.12
5131000000000	4	$  124.94	$     141.75
5131000000000	5	$  207.31	$     124.94
5131000000000	6	$  211.39	$     207.31
5131000000000	7	$  178.63	$     211.39
5131000000000	8	$  148.84	$     178.63
5131000000000	9	$  131.19	$     148.84
5131000000000	10	$  217.68	$     131.19
5131000000000	11	$  221.96	$     217.68
5131000000000	12	$  187.56	$     221.96

Period corresponds to a date, which is in a relationship to a calendar table.

The issue I am having:

1. If I use ARPU Prior = CALCULATE([ARPU],PREVIOUSMONTH('CALENDAR'[Date])), then the ARPU Prior returns blank on the visual.

2. If I use ARPU Prior = CALCULATE([ARPU],DATEADD('CALENDAR'[Date],-1,MONTH)), then the ARPU Prior is the same value as ARPU

3. If I remove the AccountNumber and Period from the visual and add 'CALENDAR'[Date], the ARPU Prior is the correct value using ARPU Prior = CALCULATE([ARPU],DATEADD('CALENDAR'[Date],-1,MONTH)).

I need the visual to show the prior month value at the accountnumber level or period level without having the date.

How can I acomplish this?

Matt

[v-shex-msft]

Hi @matthew_hampton,

Based on your table structure, I guess you use two column to mapping unique date records, right?

If this is a case, current power bi not support active multiple relationship columns between two related tables at same time.

For your scenario, time intelligence function can't correct mapping accurate date range which stored in calendar table.

So it return blank and got the correct records when you fix relationships.

Reference link:

How to fix #dax time intelligence functions in #powerbi when date table uses a surrogate key

In my opinion, I'd like to suggest you merge account number and period columns to one and do same operation on calendar table side, then use new column as the relationship column and try again.

If above not help, please share more detailed information to help us clarify your situation.

How to Get Your Question Answered Quickly

Regards,

Xiaoxin Sheng

[matthew_hampton]

Hello @v-shex-msft:

I am only using one column to map to the calendar table. I have a Calendar Table crearted with every date from 1/1/2018 through 12/31/2019 and that is related to table Promo Expire Master Lists.

Each date will have multiple Period values based on some account attributes but each Account/Period combo will only have one date.

Does this help any?

Matt

[v-shex-msft]

HI @matthew_hampton,

Please share you pbix file for test.

You can upload to onedrive or google drive and share link here.

Regards,

Xiaoxin Sheng