Forum Discussion
Calculating measure for non-existent data
Super UserHi,
Share the dowload link of the PBI file and show the expected result very clearly.
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.
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.