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
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
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,
LydiaYou just saved my life! Thank you very much, really appreciated, worked like a charm.