Calculating measure for non-existent data

[Novice_YVR]

Hi all, 

I'm working with Power Pivot but since it's DAX, it should be the same as working with Power BI I hope. 

My problem deals with missing data in my dataset. 

Let's say I have 2 month's worth of data, Dec'23 and Jan'24 and the dataset contains a list of accounts with month end balances. 

My goals are to calculate the total net change in balances, the total change in balance for accounts that increased month over month, and the total change in balance for accounts that decreased month over month. 

For example, let's say there are 3 accounts: A, B & C.

Account	Dec	Jan	MoM change
A	5	3	-2
B	6	0	-6
C	7	10	3

The total net change is -5, positive change is 3, and negative change is -8.

My previous month balance measure =CALCULATE(SUM(RepricingData[NetBalance]),PARALLELPERIOD(RepricingData[ActualDate],-1,MONTH))

My net change in balance measure =

VAR CurrentBalance = SUMX(RepricingData,RepricingData[NetBalance]) + 0

RETURN CurrentBalance - [Previous Month Balance]

The measure in the total row is correct, but when I try to calculate the measures for positive and negative changes, it doesn't work. 

I think the issue is with accounts that had a balance in Dec, but doesn't exist in Jan as per below:

The change from previous month measure should be -736.38 but since there isn't a Jan data point for this account, it's returning a blank. 

What would be the best way to correct this? I've tried the following examples and it does not work:

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Thank you. 

[Ashish_Mathur]

Hi,

Do these measures work?

Measure1 = SUMX(FILTER(VALUES(Data[Account]),[MOM change]<0),[MOM change)

Measure2 = SUMX(FILTER(VALUES(Data[Account]),[MOM change]>0),[MOM change)

[Novice_YVR]

These measures do not work.

I think the key issue is there's no data existing for account B in Jan. 

Is there a way to insert a dummy record with the balance at 0 to compensate for this? 

Thanks. 

[Ashish_Mathur]

Hi,

Share the dowload link of the PBI file and show the expected result very clearly.

[Novice_YVR]

Hi, 

Apologies as I am unable to share a PBIX file. 

I'm hoping the screenshot will suffice. 

Table 1 would be the raw data that I have; using the measures in my original post, table 2 is generated. The results as presented in table 3 is what I am expecting/seeking (This is a hardcoded table that I inserted). 

As you can see, Account B is missing in Table 2 which I suspect is because there is no record for it in Feb as per table 1. 

Thank you. 

[Ashish_Mathur]

Hi,

I cannot understand anything from the screenshots.

[Novice_YVR]

Apologies, my table 3 was incorrect. See below amended screenshot.

Explanation:

Table 1 is what the underlying data looks like.

For Table 2:

PriorMonthBalance is a measure calculated as: CALCULATE(SUM(Table1[Balance],PARALLELPERIOD(Table1[Date],-1,MONTH))

Month over month change is a measure calculated as:

SUM(Table1[Balance]) - PriorMonthBalance

Table 2 should show the change in balance from each account from Dec to Jan.

Account A Dec balance was 100 and Jan balance was 50 therefore, Month over month change = -50

Same idea for Account C.

The problem with Table 2 is it is missing Account B. Looking back to Table 1, Account B had a balance of 200 with no data point in Jan. How come the Month over month change measure is not showing -200 for account B in Table 2?

What I am expecting as an output is Table 3. It should show the month over month change for each account. 

[Ashish_Mathur]

Hi,

I will need a file to work with.  Show the expected result there clearly.  Without a file, I will not be able to help.