True or False based on multiple Variables

[Calvin69]

Hi all,

I am trying to create a new column: "Status"

Data sample:

Date	Property	Building	Location	Client	Start	End	Break Date	Value	Status
01/01/2021	Prop1	Build1	Upper Flat	Vacant				£0.00
01/01/2021	Prop1	Build1	Upper Flat	MC	01/01/2021	31/12/2021	01/08/2021	£20,000.00
19/08/2021	Prop1	Build1	Upper Flat	MC	01/01/2021	31/12/2021	01/09/2021	£20,000.00	TRUE
02/09/2021	Prop1	Build1	Upper Flat	MC	01/01/2021	31/12/2021		£20,000.00	TRUE
03/03/2023	Prop1	Build1	Upper Flat	MC	01/01/2022	31/12/2022	01/06/2022	£20,000.00	TRUE
01/01/2023	Prop1	Build1	Upper Flat	Five Guys	01/01/2023	31/12/2023	01/06/2023	£25,000.00	FALSE
02/06/2023	Prop1	Build1	Upper Flat	Five Guys	01/01/2023	31/12/2023		£25,000.00	TRUE
01/01/2024	Prop1	Build1	Upper Flat	Five Guys	01/01/2024	31/12/2024	01/06/2024	£25,000.00	TRUE
01/01/2025	Prop1	Build1	Upper Flat	Addida				£25,000.00	FALSE

Screenshot:

Logic:

• If a break date for a location changes to future date or becomes "blank" and the client remains the same, status should be = True
• If a break date for a location changes to a future date or becomes "Blank" and the client is different, Status should be = False

Obvisouly, this table will have multiple properties, multiple buildings and multiple locations.

Thanks in advance for your help

H

[v-yalanwu-msft · Accepted solution]

Hi, @Calvin69 ;

You could try to create a column by dax :

Status =
IF (
    [Client] = "Vacant"
        || ([Break Date] < TODAY ()&& [Break Date] <> BLANK ()),
    BLANK (),
    IF (
        [Break Date]= CALCULATE ( MIN ( [Break Date] ), ALLEXCEPT ( 'Table', 'Table'[Client] ) ),
        "False",
        "True"))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Calvin69]

Hi @amitchandak ,

Would you be able to assist with this query?

Thanks

H

[Vera_33]

Hi @Calvin69 

If a break date for a location changes to future date Means break date > Today or > [Date]? And you want a DAX calculated column or Power Query? Here is one way in Power Query, and use > [Date]

    #"Added Index" = Table.AddIndexColumn(yourPreviousStep, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Status", each ([Break Date]>[Date] or [Break Date]=null ) and [Client]=#"Added Index"[Client]{[Index]-1})

[Calvin69]

Hi @Vera_33 ,

Thanks for your response, I really appreciate it.

Unfortunately, following the logic you mentioned above does not really provide me with what I am after as your proposed index column, indexes the whole data set under one sequence while in fact the index should be indexing entries per Client "for each client it restart numbering from 0 to Infinite".

I have managed to create successfully an index column "Using Dax" that does the indexing per Client however, I was not able to create a index like "Index Required".. Shown below..

Data sample:

Date	Property	Building	Location	Client Index	Client	Index required	Start	End	Break Date	Value	Status
01/01/2021	Prop1	Build1	Upper Flat	1	Vacant	1				£0.00
01/01/2021	Prop1	Build1	Upper Flat	1	MC	1	01/01/2021	31/12/2021	01/08/2021	£20,000.00
19/08/2021	Prop1	Build1	Upper Flat	1	MC	2	01/01/2021	31/12/2021	01/09/2021	£20,000.00	TRUE
02/09/2021	Prop1	Build1	Upper Flat	1	MC	3	01/01/2021	31/12/2021		£20,000.00	TRUE
03/03/2023	Prop1	Build1	Upper Flat	1	MC	4	01/01/2022	31/12/2022	01/06/2022	£20,000.00	TRUE
01/01/2023	Prop1	Build1	Upper Flat	2	Five Guys	1	01/01/2023	31/12/2023	01/06/2023	£25,000.00	FALSE
02/06/2023	Prop1	Build1	Upper Flat	2	Five Guys	2	01/01/2023	31/12/2023		£25,000.00	TRUE
01/01/2024	Prop1	Build1	Upper Flat	2	Five Guys	3	01/01/2024	31/12/2024	01/06/2024	£25,000.00	TRUE
01/01/2025	Prop1	Build1	Upper Flat	3	Addida	1				£25,000.00	FALSE

I usually tend to DAX everything but I don't mind using P Query if it does deliver the right results.

Answering your question above:

If a break date for a location changes to future date Means break date > [Creation Date] - Hidden column

Somehow "Don't know yet how" the following should happen:

• A client name is "Vacant" - then status should be Blank
• A client has moved into a location "first time", in this case status = Blank
• A client has remained:
  • Multiple entries can be registered for the same location and same Client as when ever a value in any available table fields is changed a new entry is created "could be Size, Energy Code, Safety code or Break Date, etc..."
    • I need a DAX or PQuery code that would identify per tenant & location the following:
      1. "Today's Break Date Value" > "last Break Date Value" or Blank
      2. "Client Today" = "Client Last" - Then status should be True If not False

Thanks again

H