Help For Filtering the History Data in PowerBi

[Anonymous]

Hi,

Scenario: I have a history Data which consists of many records. I Just want to Filter the history record if value changes.

so now i just want to filter both

1. before change record(latest) and

2. Changed record

or can i change record field is current value to true if any field value changes?

I Have History Data in PowerBi Query in the Following Manner.

Id	Value	Date	Is Current
58667	1	07-11-2019	FALSE
58667	1	08-11-2019	FALSE
58667	1	09-11-2019	FALSE
58667	1	10-11-2019	FALSE
58667	2	11-11-2019	TRUE

How can i filter the history Data(Last Two rows only to use that in my chart) in the following Manner.

Id	Value	Date	Is Current
58667	1	10-11-2019	TRUE
58667	2	11-11-2019	TRUE

Thanks in Advance!! Have a Great Day!

[v-lionel-msft]

Hi @Anonymous ,

First, Try this measure:

Is current = 
VAR x = 
CALCULATE(
    LASTDATE(Sheet1[Date]),
    ALL(Sheet1)
)
RETURN
IF(
    MAX(Sheet1[Date]) = x,
    TRUE(),
    FALSE()
)

Second, Try this calculated table:

Table = 
SUMMARIZE(
    Sheet1,
    Sheet1[Id],Sheet1[Value],
    "Date", MAX(Sheet1[Date])
)

Best regards,
Lionel Chen

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

[Anonymous]

Thanks @v-lionel-msft 

But this does not satisfy my scenario. it fails when 

Id	Value	Date	Is Current
58667	1	07-11-2019	FALSE
58667	1	08-11-2019	FALSE
58667	1	09-11-2019	FALSE
58667	1	10-11-2019	FALSE
58667	2	12-11-2019	FALSE
58667	2	13-11-2019	FALSE
58667	3	14-11-2019	FALSE
58667	3	15-11-2019	TRUE

Den it should return

Id	Value	Date	Is Current
58667	1	10-11-2019	FALSE
58667	2	13-11-2019	FALSE
58667	3	15-11-2019	TRUE

It should get updated or saved when value changes

NOTE: Id is Same for all.

or If Record/row Value changes. can we change the Is Current value of previous record/row to true. so that we can filter by

Is Current = true

Thanks,

Sandeep.

[v-lionel-msft]

Hi @Anonymous ,

I modified the code of the calculated table. 
Is this what you want?

Table = 
SUMMARIZE(
    Sheet1,
    Sheet1[Id],Sheet1[Value],
    "Date", MAX(Sheet1[Date]),
    "Is current", [Is current]
)

"or If Record/row Value changes. can we change the Is Current value of previous record/row to true. "

If the date changes as the value changes, the "Is current" formula can achieve your needs.

If the date doesn't change when the value changes, the "Is current" formula can't take effect.

Best regards,
Lionel Chen

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

[Mariusz]

Hi @Anonymous 

You can use Group By on Id and Value, aggregating on Max Date, this will give you the below result.

Please see the attached file for Reference 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

[Anonymous]

Thanks @Mariusz 

But this does not satisfy my scenario. it fails when 

Id	Value	Date	Is Current
58667	1	07-11-2019	FALSE
58667	1	08-11-2019	FALSE
58667	1	09-11-2019	FALSE
58667	1	10-11-2019	FALSE
58667	2	12-11-2019	FALSE
58667	2	13-11-2019	FALSE
58667	3	14-11-2019	FALSE
58667	3	15-11-2019	TRUE

Den it should return

Id	Value	Date	Is Current
58667	1	10-11-2019	FALSE
58667	2	13-11-2019	FALSE
58667	3	15-11-2019	TRUE

It should get updated or saved when value changes

NOTE: Id is Same for all.

or If Record/row Value changes. can we change the Is Current value of previous record/row to true. so that we can filter by

Is Current = true

Thanks,

Sandeep.

@Mariusz