Create a prior week sum across multiple dimensions with missing data

[mbolton-maggs]

I have a weekly data set which consists of 5 dimensions in addition to the date dimension. I'm trying to create a dashboard which compares the sum in the current week for one measure against the sum in the previous week, and also allows me to slice the sum by the 5 identified dimensions.

So far, my formula reads as follows:

M:LW_Int_Amt = 
CALCULATE (
    SUM (Data[Int_Amt]),
    FILTER (ALL(Data),
        COUNTROWS(
            FILTER(Data,
                EARLIER ( Data[Wk_End] ) = DATEADD ( Data[Wk_End], -7, DAY )
                && Data[APR_Band] = EARLIER ( Data[APR_Band] )
			 	&& Data[Crdt_Set] = EARLIER ( Data[Crdt_Set] )
				&& Data[Int_Type] = EARLIER ( Data[Int_Type] )
				&& Data[Strt_Sgmtn] = EARLIER ( Data[Strt_Sgmtn] )
				&& Data[Tenure] = EARLIER ( Data[Tenure] )
				))))

This gives me the functionality that I want, but ultimately doesn't give the right answer. The reason for this is because this formula will only return the prior week value for measures in dimensions for which a value exists in the current week. 

So to give an example, for the combination

[APR_Band] = "0 to 9.9"
[Crdt_Set] = "B. 7"
[Int_Type] = "Int_Revolving"
[Strt_Sgmnt] = "A. New"
[Tenure] = "5-10 Years"

There is a value of £19.00 in week 32, but nothing in week 33. So, the week 33 prior week number for this combination returns £0.

Any help would be much appreciated! 

[v-ljerr-msft]

Hi @mbolton-maggs,

There is a value of £19.00 in week 32, but nothing in week 33. So, the week 33 prior week number for this combination returns £0.

---

What do you mean about "nothing in week 33"? Do you mean the value is £0 for week 33, or there is no any data for week 33 in the "Data" table?

Does all the related data in the same table? Could you post your real table structures with some sample data which can reproduce the issue? It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards

[mbolton-maggs · Accepted solution]

Hi @v-ljerr-msft,

To clarify, I mean that there is no data for that combination of dimensions in the data table. If there were data but it showed a value of £0, then the EARLIER statements would work and return the corresponding prior week value of £19 for that amount. 

I've actually worked out a way around this by building a new table as follows:

F:Int_Tot = CROSSJOIN(FILTER(
		SELECTCOLUMNS(Cal_Wk,
			"Stmnt_Wk", Cal_Wk[Stmnt_Wk],
			"LW_Stmnt_Wk", Cal_Wk[LW_Stmnt_Wk],
			"LY_Stmnt_Wk", Cal_Wk[LY_Stmnt_Wk],
			"Wk_End", Cal_Wk[Wk_End]),
			[Wk_End] >= DATE(2015, 07, 04)),
                'Dim:APR_Band',
                'Dim:Crdt_Set',
                'Dim:Int_Type',
                'Dim:Strt_Sgmnt',
                'Dim:Tenure')

This gives me a complete data table (i.e. with no missing dimension combinations) that allows me to build up all the necessary current week / prior week values as needed. 

Each of the Dim tables are also created through SUMMARIZE on the original data table.

I'd love to post a sample table, but my challenge is that I'm working with a very large, commercially sensitive data set (original data table is 90k rows * 30 columns), and my level of Power BI knowledge is such that it would take a very long time to create a summarised, de-sensitised table.

If you have any other angles to this challenge, then that would be much appreciated.