Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
supsavvy
New Member

Need help with time series metric on ledger style data.

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.

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1686191443566.png

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 :

vyueyunzhmsft_1-1686191522865.png

 

 

 

 

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

 

 

 

View solution in original post

1 REPLY 1
v-yueyunzh-msft
Community Support
Community Support

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:

vyueyunzhmsft_0-1686191443566.png

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 :

vyueyunzhmsft_1-1686191522865.png

 

 

 

 

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

 

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors