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 Canvas 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.
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.
Please do help @EnterpriseDNA @SamMcKay @BrianJ
The total Distinct STore is calcualted as
Total Distinct Store =
VAR StarDate = STARTOFMONTH(DATEADD(‘Date’[Date],-2,MONTH))
VAR FinalDate = MAX(‘Date’[Date])
VAR DistinctStoreTillPreviousEndDate = CALCULATETABLE(
VALUES(‘Main Fact Table’[Store Number]), DATESBETWEEN(‘Date’[Date],StarDate, FinalDate),
ALL(‘Main Fact Table’[Product]), ALL(‘Main Fact Table’[Brand])
)
VAR DistinctStoreTillCurrentEndDate = CALCULATETABLE(
VALUES(‘Canvas Store’[Store Number]),
ALL(‘Main Fact Table’[Product]), ALL(‘Main Fact Table’[Brand])
)
RETURN
COUNTROWS(
INTERSECT(DistinctStoreTillCurrentEndDate, DistinctStoreTillPreviousEndDate)
)