Need help with time series metric on ledger style data.

[supsavvy]

I have a ledger containing the history of changes of an item attribute (we’ll call it "foo") for various items. I need to create a line graph that can show the average foo value, over time, across all items. Users need to be able to drill up/down to see the trend by day, week, month, etc.

The measure needs to find the effective foo value at any date. This would be the latest update in the ledger, for an item, that occured before or on the reporting date.

Note: Creating a table of daily "snapshots" of foo for each item in the data model is not a possibility, due to dimensionality (millions of items and foo ledger records).

I have three tables:

• ‘foo Ledger’ – A table which contains a record every time “foo” was updated for an item:

Site:Item	foo	Date Updated	Date.Time
1:123	15	11/15/21	(Julian date and numeric time of update)
1:123	25	1/1/22	…
2:123	12	1/2/22	…

• ‘Items in Scope’ - A one column table containing the unique items which are in scope for this analysis:

Site:Item

1:123

2:123

3:345

• ‘Fiscal Dates’ – A table that relates dates to fiscal week, month, quarter, etc. The visual will need to be able to trend by the various fiscal buckets:

Date	Fiscal Week	Fiscal Quarter	Fiscal Month
12/31/2021	39	Q3	M9
1/1/2022	40	Q4	M10
1/2/2022	40	Q4	M10

Here is my attempt at such a measure. The values returned are astronomically high, so I know it is wrong. I'm essentiall using the item scope table as the base for a virtual table, adding a column that searches for the foo value in the ledger for each item (within the date context), and the returning the average of the new virtual column.

Measure =

var fooTable =

SUMMARIZE(

'Items In Scope',

'Items In Scope'[Site:Item],

"foo",

CALCULATE(

MAX('foo Ledger'[foo]),

FILTER('foo Ledger',

'foo Ledger'[Date.Time] = CALCULATE(

MAX('foo Ledger'[Date.Time]),

'foo Ledger'[Date Updated] <= MAX('Fiscal Dates'[Date]),

'foo Ledger'[Site:Item] = SELECTEDVALUE('Items In Scope'[Site:Item])

)

)

)

)

return

AVERAGEX(

FILTER(

fooTable,

NOT [foo] IN {BLANK(), 0, 111, 222, 333}

-- 111, 222, 333 are values that should not be included in the average, as these are placeholders for “foo”

),

[foo]

)

Any help is greatly appreciated.

[v-yueyunzh-msft · Accepted solution]

Hi , @supsavvy 

According to your description, you want to " create a line graph that can show the average foo value, over time, across all items. Users need to be able to drill up/down to see the trend by day, week, month, etc.".

Here are the steps you can refer to :
(1) This is my test data, you can see it in the pbix file:

And there is no relationship between tables.

(2)We can create a measure like this:

Measure = var _max_date= MAX('Fiscal Dates'[Date])
var _slicer = VALUES('Items in Scope'[Site:Item])
var _t =  SUMMARIZE('Items in Scope' , 'Items in Scope'[Site:Item] , "foo" , var _item = [Site:Item] var _t2 = FILTER('foo Ledger' , 'foo Ledger'[Site:Item] = _item && 'foo Ledger'[Date Updated]<=_max_date && 'foo Ledger'[Site:Item]=_item && NOT('foo Ledger'[foo] in {BLANK(), 0, 111, 222, 333})) var _max_dateTime = MAXX(_t2 , [Date.Time])   
 var _t3 =  FILTER(_t2,[Date.Time]=_max_dateTime)  return MAXX(_t3, [foo]))
return
AVERAGEX(_t,[foo])

Then we can put the measure and the fields we need on the visual :

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly