Finding number of product/brand on latest visit date

[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 to find the Total Number of Store id 

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)

    )

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.

The total distinct store and latest site visit date is ok but I am unable to find the if that particular store has brand BB on that site visit.  I have a date slicer which is Aug 2023 at the moment. From the below figure , there is no context transition. The code only gives the result for August (which is the selected value in Slicer called Date[Date], but does not do backward upto July and June.

I should be getting 1 on 14/07/2023. The code above ignore all the date in July and June.

Please do help @PerryW @Amit @Greg 

[lbendlin · Accepted solution]

yes, you would need to use a disconnected table to feed the slicer.

[Mkrishna]

Thank you so very much for the help. I will give this a try. @lbendlin 

[Mkrishna]

Hi,
In the date slicer, when I selecte month (lets say August), I want to go the measure filter date from June, July and August. If the month selected is May then the measure should look into May, April and March. This is what I want my measure to do.

Even when I make a matrix view and put month in column, the number that measure output should look into three month i.e. if the month is August 2023 in column in matrix, then the measure should give the output based on (August, July and June)

[lbendlin]

Let's say it's August 1st. Then you would get data for 62 days. Then on August 31st you would get data for 92 days?

[Mkrishna]

As the number of days keeps chaning. I had put slicer with month and year only. So that when user select August 2023 in slicer, the code would go from June 01,2023 to August 31, 2023.

[MarceloVieira]

From the below figure , there is no context transition. The code only gives the result for August (which is the selected value in Slicer called Date[Date], but does not do backward upto July and June.

If you want to show 3 months on a visual + slicers that share columns from the same date table, it will not work. The slicer will hide other months. You probably should have date slicer that shares no relationship with the model, but you can explore that slicer selection.

Its more complex work like that because you will need more dax code, and the idea is to filter your original date table, based on the selection of the not related date table. a variable with "selectedvalue( unrelated_dt_table[date or month or etc] )" is a start for the rest of your work.

[Greg_Deckler]

@Mkrishna First, I would abandon DAX time intelligence functions. Those are miserable to debug and determine where things are going wrong. Second, I would use DISTINCT instead of VALUES As VALUES can return a blank row that can cause problems. Third, I wouldn't use CALCULATE but you may be able to get away with it. 

More importantly, would need sample data posted as text or a link to a PBIX file to troubleshoot properly.

You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

[Mkrishna]

@Greg_Deckler 
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]

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

only works for the current context and is most likely not what you intended.

[MarceloVieira]

@lbendlin  i understand he wants select 1 date or month, and see in the matrix the data of the last 3 months, with the current month included. But if the slicer is using a date from the same table of the dates on the matrix, filtering the slicer, would filter the visual, not showing all the 3 months.

As of my knowledge right now, i think a way to do that is using a duplicated date table, not related to the fact table, because the slicer would not filter the months on the matrix visual, but that selection can be explored to filter correctly the matrix.