Calculate table with loop

[Anonymous]

hello PBI masters,

I am a n00b of PBI and struggling with creation of a calculated table. My starting dataset is a table that, for every item and every month, provides specific information:

item	item description	FY	Month	dimension	Quantity
ABC	ABC-Description	2021	1	Ordered	55
ABC	ABC-Description	2021	1	forecast current month	60
ABC	ABC-Description	2021	1	forecast m-1	70
ABC	ABC-Description	2021	1	forecast m-2	70
ABC	ABC-Description	2021	1	forecast m-3	100

The calculated table I need is caculating, for every item and month, the difference between the row "Ordered" with each of the rows "forecast", i.e. the column "FCA" in the below example.

item	year	month	FCA
Iterm ABC	2021	1	Ordered - forecast current month
item ABC	2021	1	Ordered - forecast m-1
item ABC	2021	1	Ordered - forecast m-2
item ABC	2021	1	Ordered - forecast m-3

as far as I understand, it should require DAX and a loop / for cycle, but have no experience in this language. Anyone that could help me please?

thanks a lot!!!

[Anonymous]

works perfectly. Thanks

[PaulDBrown · Accepted solution]

I suggest you change the structure of the original table in Power Query by pivoting the "Dimension" and "Quantity" columns as follows:

 I would then suggest you create new dimension tables for Item using:

Item Table = 
SUMMARIZE('Source Table', 'Source Table'[item], 'Source Table'[item description])

and for Year and Month following this pattern:

Fiscal Year Table = DISTINCT('Source Table'[FY])

Set up the model using single direction one-to-many relationships between the dimension tables:

Create the measures you need following this pattern:

Ordered - forecast month = SUM('Source Table'[Ordered])  - SUM('Source Table'[forecast current month])

Ordered - forecast m-1 = SUM('Source Table'[Ordered])  - SUM('Source Table'[forecast m-1])

Finally set up a matrix using the fields from the dimension tables and the measures. In the formatting pane, under Values -> Options, turn on the option "Switch values on rows" to get:

Attached is the sample PBIX file