Last visit filter depending on date (part II)

[Anonymous]

Hi,

So we come from here; https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Variable-column-depending-on-filters... 

The original need was to get a Last visit filter: Normally we evaluate the presence of our products in a number of shops. So, it is usefull to have the number of centers with our products in the last date we checked. I could solve it making a measure that selects lastdate of each center, but what I need is the possibility to have the same visuals calculating presence-related measures and a possibility to filter "Is it last visit? Yes/No, this will depend on date selected.

example: if we select all the dates of the following table, Presence with "Is it last visit?= Yes" will add up to 2. Contrary to that, "Is it last visit= Yes and No" filter, the result will be 5.

Shop//Date of visit(DD/MM/YYYY)//Presence of our product

Center A	01/01/2023	YES
Center B	02/01/2023	NO
Center C	04/01/2023	YES
Center D	15/01/2023	NO
Center B	02/02/2023	YES
Center B	04/02/2023	YES
Center A	08/02/2023	NO
Center C	09/02/2023	NO
Center D	02/02/2023	NO
Center A	10/02/2023	YES

The development provided by @v-yiruan-msft  is correct, but it only works when you want to get the detail of a Yes presence, or a No presence. When you want to see the details of YES and NO in a visual, it is not accurate for some reason.

The original development is based in three measures:

Last visit filter = 
VAR _mindate =
    MIN ( 'CALENDAR'[Date] )
VAR _maxdate =
    MAX ( 'CALENDAR'[Date] )
VAR _selshop =
    SELECTEDVALUE ( 'Date of visit'[Shop] )
VAR _selvisitdate =
    SELECTEDVALUE ( 'Date of visit'[Date of visit] )
VAR _lastvisitdate =
    CALCULATE (
        MAX ( 'Date of visit'[Date of visit] ),
        FILTER ( ALLSELECTED ( 'Date of visit' ), 'Date of visit'[Shop] = _selshop )
    )
RETURN
    IF (
        _selvisitdate < _mindate
            || _selvisitdate > _maxdate,
        BLANK (),
        IF (
            _selvisitdate = _lastvisitdate
                && _selvisitdate >= _mindate
                && _selvisitdate <= _maxdate,
            "Y",
            "N"
        )
    )

Presence Yes = 
VAR _filter = [Last visit filter]
VAR _tab =
    SUMMARIZE (
        'Date of visit',
        'Date of visit'[Shop],
        'Date of visit'[Date of visit],
        'Date of visit'[Presence of our product],
        "@lastvisit", _filter
    )
RETURN
    COUNTX (
        FILTER (
            _tab,
            [@lastvisit]
                IN ALLSELECTED ( 'Last Visit'[Last Visit] )
                    && [Presence of our product] = "NO"
        ),
        [Date of visit]
    )

Measure = 
SUMX (
    GROUPBY (
        'Date of visit',
        'Date of visit'[Shop],
        'Date of visit'[Date of visit]
    ),
    [Presence Yes]
)

And I have tried removing the "&& [Presence of our product] = "NO"" part, but as you can see in the PBI attached here, it wont work when we use "presence of our product" column to describe the leyend. The correct number is "No"=232, but if we use the column to describe we get 242 instead. 

I must be missing something. Ill give you the sample data here and de PBIX file:

https://drive.google.com/file/d/177di68NHMkjZ3-ovYjYq4jmirXY76YKq/view?usp=sharing 

https://docs.google.com/spreadsheets/d/1EMqQVraRaQrSxW02-RbgMV6XBa9nclSx/edit?usp=sharing&ouid=10280... 

Thanks a lot in advance!

[v-yanjiang-msft]

Hi @Anonymous ,

After long time research, I'm still not quite clear about your sample and purpose. As your description "I have tried removing the "&& [Presence of our product] = "NO"" part", but why not remove it, here's the result of remove the part.

Best regards,

Community Support Team_yanjiang

[Anonymous]

Hi v-yanjiang, thanks for your answer and I apologize if i didnt make myself clear.

The measure, as you stated above in your picture, works, but it is not correct. For instance, the correct number of "No"s is 232 as I showed in my sample, not 242. Also, if you check your numbers in the table, they dont add up 242+215+2 is not 445. Correct number is NO= 232 + YES = 211 BLANK=2, and that is 445!

Hope I cleared it out.