Forum Discussion
Calculated measures comparing data by initial and final date
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
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.
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
You just saved my life! Thank you very much, really appreciated, worked like a charm.