cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
giovanniliv
Frequent Visitor

Column that filters for specific, multiple columns and returns conditional output based on datediff

I have a table of outlets visits done by sales representatives. This table is built like this:

VisitID | Date | OutletId | ProductName | IsPresent 

VisitID: numerical identifier for a given visit.
Date: Date when visit took place.
OutletID: numerical identifier for the point of sale.

ProductID: beverage product numerical ID (products arechecked by the sales representative when visiting the outlet).
IsPresent: binary 1 or 0 value that marks 1 when the item is present and 0 when is not present for the specific visit.


A sample of the table is below (only for a certain date, just to illustrate the data structure). I have also an excel with sample data here on wetransfer: https://we.tl/t-P2V6TMrh7n

VisitID   Date    OutletID    ProductID    IsPresent

170267  12-Jun-21    9639    286FALSE
170267  12-Jun-21    9639    229FALSE
170267  12-Jun-21   9639    234FALSE
170267  12-Jun-21  9639    317FALSE
170267  12-Jun-21  9639    215TRUE
170268  12-Jun-21   9854    286FALSE
170268  12-Jun-21   9854    317FALSE
170268  12-Jun-21    9854    234TRUE
170268  12-Jun-21    9854    215TRUE
170268  12-Jun-21    9854    229TRUE
170269  12-Jun-21    10062    286FALSE
170269  12-Jun-21    10062    229FALSE
170269  12-Jun-21    10062    317FALSE

 

My goal is to create a calculated column that marks as:
- "Available" when product is present (so when IsPresent = 1)
- "Out of Stock when product is not present (so when IsPresent = 0) AND in the last 90 days there has been at least one visit that marked the same product as present. 

- "Never Available" when product is not present  AND in the last 90 days there was no visit that marked the same product as present.

 

I have tried to follow a previous post of mine and I came up with this calculated column with no luck:

 

 

IsPresent2 =
VAR OutletID = ProductStatuses[OutletID]
VAR BeginDate = ProductStatuses[Date]
VAR ProductID = ProductStatuses[ProductID]
VAR vTable =
    ADDCOLUMNS (
        CALCULATETABLE (
            ProductStatuses,
            ProductStatuses[ProductID] = ProductID,
            ProductStatuses[IsPresent],
            ProductStatuses[OutletID] = OutletID,
            ProductStatuses[Date] <= BeginDate,
            ProductStatuses
        ),
        "DateDiff", DATEDIFF ( ProductStatuses[Date], BeginDate, DAY )
    )
RETURN
    IF (
        ProductStatuses[IsPresent] = 1,
        "Present",
        IF (
            CALCULATE (
                MAX ( ProductStatuses[IsPresent] ),
                FILTER ( vTable, [DateDiff] < 90 )
            ) = 1,
            "Out of Stock",
            "Never Available"
        )
    )

 

Any help is very appreciated!
Thank you 🙂

1 ACCEPTED SOLUTION
DataInsights
Super User II
Super User II

@giovanniliv,

 

Try this calculated column:

 

IsPresent2 = 
VAR vVisit = ProductStatuses[VisitID]
VAR vProduct = ProductStatuses[ProductID]
VAR vOutlet = ProductStatuses[OutletID]
VAR vTable =
    FILTER (
        ProductStatuses,
        ProductStatuses[VisitID] = vVisit
            && ProductStatuses[ProductID] = vProduct
            && ProductStatuses[OutletID] = vOutlet
    )
VAR vMaxDate =
    MAXX ( vTable, ProductStatuses[Date] )
VAR vVisitCountProductPresent =
    COUNTROWS (
        FILTER (
            vTable,
            ProductStatuses[Date] >= vMaxDate - 90
                && ProductStatuses[Date] < vMaxDate
        )
    )
VAR vResult =
    SWITCH (
        TRUE (),
        ProductStatuses[IsPresent] = 1, "Available",
        ProductStatuses[IsPresent] = 0
            && vVisitCountProductPresent >= 1, "Out of Stock",
        "Never Available"
    )
RETURN
    vResult

 

DataInsights_0-1624369535511.png

 

View solution in original post

2 REPLIES 2
DataInsights
Super User II
Super User II

@giovanniliv,

 

Try this calculated column:

 

IsPresent2 = 
VAR vVisit = ProductStatuses[VisitID]
VAR vProduct = ProductStatuses[ProductID]
VAR vOutlet = ProductStatuses[OutletID]
VAR vTable =
    FILTER (
        ProductStatuses,
        ProductStatuses[VisitID] = vVisit
            && ProductStatuses[ProductID] = vProduct
            && ProductStatuses[OutletID] = vOutlet
    )
VAR vMaxDate =
    MAXX ( vTable, ProductStatuses[Date] )
VAR vVisitCountProductPresent =
    COUNTROWS (
        FILTER (
            vTable,
            ProductStatuses[Date] >= vMaxDate - 90
                && ProductStatuses[Date] < vMaxDate
        )
    )
VAR vResult =
    SWITCH (
        TRUE (),
        ProductStatuses[IsPresent] = 1, "Available",
        ProductStatuses[IsPresent] = 0
            && vVisitCountProductPresent >= 1, "Out of Stock",
        "Never Available"
    )
RETURN
    vResult

 

DataInsights_0-1624369535511.png

 

View solution in original post

Hi @DataInsights,

I have looked at your code and then again at my code and I think both works 😄 
I don't know why it was giving me an error before but I have tried again and now it seems to be working. 

I will accept your as solution though! Thank you for the help 👍


Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors