Calculated measures comparing data by initial and final date

[Anonymous]

Good afternoon,

I am struggling with Power BI's calculated measures + data in tables. The problem I am trying to solve is the following:

The data has an Stated Update Date, which I have linked with my Calendar table. The user selects a range of dates. The idea is:

• I need the list of Products that in the Initial Date had a State 1 or 2, and in the Final Date have a state 3 or 4.
• So the user filters, and I can get the list of records of materials in that date range.
• For evert record, I can show the Previous State and the Current State for every record in that range.
• But can I get only those who have moved from 1,2 to 3,4, attending only to the Initial and Final dates?

I have problems with calculated measures, as when I try to get the current state for a Product, I can't put it in a calculated measure, as the Previous State and the Current State are data in a table, I does not give me the option to 'filter' it.

I hope I have explained myself; I'll give more information if this does not suffice.

Regards,

Antonio

[v-yuezhe-msft]

@Anonymous,

Could you please share dummy data of your table and post expected result in table format? Also we need to know that which date field you use to create the filter/slicer. 

Regards,
Lydia

[Anonymous]

Yes, my bad.

The date I am filtering is a string of format YYYYMMDD, and it is linked to a Calendar table with Date datatype.

Some dummy information:

Product, Date, State Old, State New

Product 1, Date 1, State 1, State 4

Product 1, Date 2, State 4, State 1

Product 1, Date 3, State 1, State 2

Product 1, Date 4, State 2, State 3

Product 2, Date 5, State 2, State 3

Product 2, Date 6, State 3, State 2

Product 2, Date 7, State 2, State 4

Product 3, Date 1, State 1, State 3

Product 3, Date 5, State 3, State 1

And filtering dates between Date 1 and Date 7, I should get:

Product 1, Yes

Product 2, Yes

Product 3, No

Logic: in the dates filtered, at the start of the period, Products 1 and 2 where in States 1, 2 and now are in States 3, 4 (Yes -> have changed); and Product 3 is in State 1 both and the start and end dates (No: has not changed).

Regards and thank you for your prompt response.

[v-yuezhe-msft · Accepted solution]

@Anonymous,

Create the following measures in your table.

Mindate = CALCULATE(MIN(Table[Date]),ALLSELECTED(Table[Date]),ALL(Table[State New]),ALL(Table[State Old]))
maxdate = CALCULATE(MAX(Table[Date]),ALLSELECTED(Table[Date]),ALL(Table[State New]),ALL(Table[State Old]))
initial state = CALCULATE(FIRSTNONBLANK(Table[State Old],Table[State Old]),FILTER(Table,MIN(Table[Date])=[Mindate]))
state = CALCULATE(FIRSTNONBLANK(Table[State New],Table[State New]),FILTER(Table,MAX(Table[Date])=[maxdate]))
ifchange = IF([initial state]=[state],"No","Yes")

I use the date field in the table to create a slicer.



Regards,
Lydia

[Anonymous]

You just saved my life! Thank you very much, really appreciated, worked like a charm.