cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DmitriiK
Frequent Visitor

"Time machine"-like report for versioned fact table

I'm looking to visualize state of a certain items over time. Each item has its state logged into a sort of a changelog (example):

ItemIdUpdatedStatus
Raven12.04.2021Open
Raven18.04.2021Closed
Boogie19.04.2021Resolved
Raven20.04.2021Resolved
Raven23.04.2021Closed

 

The goal is to be able to produce a similar set of measure-based report over this sample data:

DateTotal countCount of "Closed"Count of "Resolved"
18.04.2021110
19.04.2021211
20.04.2021202
21.04.2021202
22.04.2021202
23.04.2021211
24.04.2021211
25.04.2021211


Note that even if version history has no data for 21 and 22.04.2021, the resulting report contains corresponding rows with correct values. Measure must produce result per any given point of granularity (be it day or hour or anything in general); this means for a table-like report with daily granularity used in the example, there must be rows for each day.

In general, I want to pick a point of time and calculate required aggregates (count() by status in this example) over data as it existed at this point of time. This seems to be quite common task but unfortunately, I wasn't able to find any examples.

EDIT: Sample data modified to include scenario when item changes it's state back and forth.
EDIT2: Stressed out that there must be no gaps in the report (measure must be calculated for each consecutive day if we use daily granularity, for each hour if we use hourly and so on)

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

11 REPLIES 11
v-yangliu-msft
Community Support
Community Support

Hi  @DmitriiK  ,

Here are the steps you can follow:

1. Create calculated table.

Date = SELECTCOLUMNS('Table',"Date",'Table'[Updated])

2. The relationship between connecting two tables

3. Create calculated column.

Count of "Closed" =
var _1=CALCULATE(COUNT('Table'[Status]),FILTER(ALL('Table'),'Table'[Status]="Closed"&&'Table'[Updated]=MAX('Date'[Date])))
return
IF(_1=BLANK(),0,_1)
Count of "Resolved" =
var _1=CALCULATE(COUNT('Table'[Status]),FILTER(ALL('Table'),'Table'[Status]="Resolved"&&'Table'[Updated]=MAX('Date'[Date])))
return
IF(_1=BLANK(),0,_1)
Total count = 'Table'[Count of "Closed"]+'Table'[Count of "Resolved"]

Result:

v-yangliu-msft_0-1619688530228.png

 

If the data is grouped by hours, I changed the data presentation without changing the Dax function:

2021.4.291.png

Result
The data is grouped by hour Count

v-yangliu-msft_2-1619688530237.png

Does this result meet your expectations.

 

 

Best Regards,

Liu Yang

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

Hello Liu!..

I do not see how calculated table of this kind changes anything - it's basically the same as version history table. In your updated example result still contains gaps:

DmitriiK_0-1619692615603.png

The goal is to get count of items that had a specific status at specific point of time with given granularity - based on a "version history" log which does NOT have any specific granularity.

P.S. For some reason I am unable to post complete example here; it raises an error about message containing invalid HTML characters - which is infuriating as the message was totally composed by built-in editor...

v-yangliu-msft
Community Support
Community Support

Hi  @DmitriiK  ,

According to your description, I create some data:

v-yangliu-msft_0-1619591562674.png

Here are the steps you can follow:

1. Create measure.

Count of "Closed" =
var _1=CALCULATE(COUNT('Table'[Status]),FILTER(ALL('Table'),'Table'[Status]="Closed"&&'Table'[Updated]=MAX('Table'[Updated])))
return
IF(_1=BLANK(),0,_1)
Count of "Resolve" =
var _1=CALCULATE(COUNT('Table'[Status]),FILTER('Table','Table'[Status]="Resolved"&&'Table'[Updated]=MAX('Table'[Updated])))
return
IF(_1=BLANK(),0,_1)
Total count =
'Table'[Count of "Closed"]+'Table'[Count of "Resolve"]

2. Result. 

v-yangliu-msft_1-1619591562677.png

 

Best Regards,

Liu Yang

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

Hello.

Unfortunately, this will not work with a version table. A version table is a table where only changes to item state are logged: if item has no state changes over a certain period of time - there are no corresponding rows. And without them, we have 0s (incorrect values) for count of whatever status for dates where there are no rows.

In your example source data contains redundant rows: there is a row for every item and every day even if the state didn't change. For example, 2nd row here is redundant and wouldn't be present in a version table:

DmitriiK_0-1619604317836.png


This could be a solution if Power BI allowed merging a CALENDARAUTO() table with a version table - but it doesn't, date table doesn't even appear in the table list for merge operation.

Then again, what if we need pre-hour granularity instead of per-day? Per-minute?

DmitriiK
Frequent Visitor

Any ideas here?

DmitriiK
Frequent Visitor

Hello Ashish. Thank you for the example file. I tried to understand the logic mehind the measures, however it seems there are several issues with the proposed solution:


1. Original data might span several years to DATESYTD does not apply here. I tried changing it to DATESBETWEEN and result was immediately incorrect.

2. The item might change its status back and forth. It could be in the same state more than once in a given period. If you modify the source data to include this case, the results are again incorrect (for example, negative values)

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

Hello Ashish.

 

This would only work if the version history table contains exactly one row per date period you need in the report. In your example, there is exactly one row per day per item.

 

This is not the case for real datasets where item status change is not logged daily (hourly etc), but instead only logged when item status changes.

Hi,

That is exactly what my solution does.  Please study the transformation steps in the Query Editor very carefully.  I have transformed your data to make it appear as one row per date.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Well I marked this as solution, as it seemed to be the one, but I was wrong. This solution only applies when certain assumption about changelog data are valid. For example, you would get a type conversion error if any of the dates in changelog is different from 0:00:00 of a given date (e.g. contains time part):

DmitriiK_0-1620389175097.png

 

In addition, the result would be incorrect if changelog contains more than one record for a given day period - because of the way the transformation parses the data.

This is very important that no assumptions regarding source data (changelog) can be made. It contains version history in format of timestamp-item-new status, but that's it. It's not guaranteed to have only one record per day, or per hour, or per minute. 

Ashish_Mathur
Super User
Super User

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!