Forum Discussion
Calculated measures comparing data by initial and final date
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
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
4 Replies
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