Basic Data Modelling

[jasgun]

Hi all,

I have a basic model that I would like create in Power BI, but I'm having issues!

I have a Fact table that I would like to track Policy Ref over months on a cumulative basis.

Fact table is below, theAmount measure is movement in the month:

Fact table - OBM_Reconciliation

The basic result I need is:

I've got as far as this:

• Created a new table with all accounting preiods from 201506 to 201901.
• Created a new measure to calculate the cumulative position using the running total quick measure. This works:

So then I created a relationship to the new accounting period table I created to get culumative figures for months even if there wasn't a transaction.

However the two issues I have are:

1. As the accounting month I use in the visual is based on the new accounting period table, the cumulative total doesn't work.
2. It still only gives me accounting periods where there is a transactions for it.

Can someone please help me to set this up correctly? It's driving me mad!

Seems simple in my mind!!

Thanks

Jason

[v-juanli-msft · Accepted solution]

Hi @jasgun 

You need to create a table with all month period (eg, for this example, 201506~201804) for each "Policy Ref",

leave "Amount" for some period blank, (eg, in this example, "Amount" for 201507, 201508 are blank)

->these could be done with Power Query

then fill blanks with previous value

->see similar thread:

https://community.powerbi.com/t5/Desktop/Fill-blanks-with-previous-value/td-p/492501

Best Regards

Maggie