Finding Number of Brand in Specific Date using DAX

[Mkrishna]

Hi All,

I am doing a data analysis and I need to find the if the Brand (BBB) is present in the latest visit or not. This is my data model

The State dimension is connected to the Main Fact Table dimension.
The Date dimension is connected to the Visit Date of the Main Fact Table
The Store number of the Store is connected to the store number of Main Fact Table.

The main Fact table has Brand, Product, Store, Date column.

The Main Fact Table has main data. For example, store id 100, can be visited at 1/08/2023, 23, 23/06/2023, 22/05/2023.

I used the following DAX code to get the Latest Visit date with last three month from the date specified in Date Slicer.

Latest Visit Date =
VAR StarDate = STARTOFMONTH(DATEADD(‘Date’[Date],-2,MONTH))
VAR FinalDate = MAX(‘Date’[Date])

RETURN
CALCULATE(
MAX(‘Main Fact Table’[Visit Date]),DATESBETWEEN(‘Date’[Date], StarDate, FinalDate)
)

Example. If the Date slicer has August then the latest site visit date is 01/08/2023(It look data from August, June, July). However if the date in the slicer is July, the the code will look from July , June and May. In July, it wasn’t visited and hence the latest site visit date is June 23, 2023.

After finding the latest site visit date, I want to find if on that latest site date, is there product of Brand BBB or not. If present, give 1, else give 0 and then give sum as total.

BBB=

VAR StartDate = [Latest Visit Date]

VAR EndDate = MAX('Date'[Date])

RETURN

    CALCULATE(

        DISTINCTCOUNT('Main Fact Table'[Store Number]),

        FILTER('Main Fact Table',

            'Main Fact Table'[Visit Date] >= StartDate &&

            'Main Fact Table'[Visit Date] <= EndDate &&

            'Main Fact Table'[Brand]= "BBB"

        )

    )

Find the latest site visit date seems ok but the second part I am having problem.
The visual is as follows

I should be getting 1 on 3/07/2023. The code above ignore all the date in July and June. The slicer has August selected.

Please do help

[lbendlin]

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Mkrishna]

@lbendlin 

Thank you for your response. Really appreciated. 
Please do find the link to the file
https://drive.google.com/file/d/1_MSn5wUh063PWk4d7Qx3e0MC1m_9pa62/view?usp=drive_link

Store : Is the total store in the company
Fact Table: Shows the data from May to August for the companies that have been visited.

Latest Visit Measure : It gives the latest date for the unit visited in past three months (If August is selected in Date slicer, then it looks into August, June and July) and give the latest date of visit.

Total Store: gives the total distinct store visited in past three month (If August is selected then it looks into August, July and June).

Now, I want to find if the store has brand Coca Cola in its latest visit. If yes, give 1 else give 0. Finally Sum for total

Also, I also want to find the sum of Fridge store brand Coal Cola in the latest visit and get the Total Store of Fridge in the latest visit.

[lbendlin]

Please check the link and remove the access restriction.