i have flight data as below
person column, status column, date column
person1, fly in, 1/2/21
person1, fly out, 11/2/21
person2, fly in 3/2/21
person2, fly out, 23/2/21
how do i create a new table or colum to expand the dates in between with the following rules:
Dates beteween fly in and fly out is filled in with status Work
Dates between fly out and fly in is filled with status Home.
Thanks in advance.
You can try to use following calculate table formula to expand raw table and replace the status:
VAR _calendar =
MINX ( ALL ( 'Sample'[record date] ), [record date] ),
MAXX ( ALL ( 'Sample'[record date] ), [record date] )
CROSSJOIN ( VALUES ( 'Sample'[person] ), _calendar ),
VAR currPerson = [person]
>= MINX ( FILTER ( ALL ( 'Sample' ), [person] = currPerson ), [record date] )
<= MAXX ( FILTER ( ALL ( 'Sample' ), [person] = currPerson ), [record date] )
IN SELECTCOLUMNS (
FILTER ( ALL ( 'Sample' ), [person] = EARLIER ( 'Sample'[person] ) ),
"Date", [record date]
Thanks Xiaoxin @v-shex-msft
This works nicely as in the first 2 columns below (Dates, Query Status). However, the actual status is in the "Actual Status" column.
Back to original data i posted earlier, how do i change the DAX to make use of column with status "Fly in", Fly out" to get the Query status to match the Actual status plesae?
table for the above
Based on the sampele data you have provided, create a new table as below
_Work = SUMMARIZE(table,table[person],"fly in",CALCULATE(Min(table[date]),ALLEXCEPT(table,table[person])),"fly out",CALCULATE(Max(table[date]),ALLEXCEPT(table,table[person])))
Learn how to create your own user groups today!
Click here to read more about the October 2021 Release!
Check out the new Power Platform Community Connections gallery!
Mark your calendars and join us for our next Power BI Dev Camp!