How to get count of customer ID for the previous week based on multiple selection of date

[mohittimpus]

In table I have a "Date" column, "Customer ID" column & "Category" Column, now what I need is that if we select multiple dates in slicer then it must return the count of "Customer ID" for the previous for all selected dates in slicer.

I have created below measure but it work on single selection, not on multiple selection:

Previous Week = var SelectedDate = SELECTEDVALUE(Sheet2[Date])

var PreviousWeek = SelectedDate-7

return CALCULATE(COUNT(Sheet2[CustomerID]), Sheet2[Date]=PreviousWeek)

Please find the attached .pbix file link below:

https://drive.google.com/file/d/1e7uC84v0gjT6dYiOavrCnwhPCKQdXkHD/view?usp=sharing

Thanks!

[v-xicai]

Hi @mohittimpus ,

You may add ALLSELECTED function in your formula like DAX below to take effect of the slicers or filters.

Previous Week = var SelectedDate = SELECTEDVALUE(Sheet2[Date])
var PreviousWeek = SelectedDate-7
return CALCULATE(COUNT(Sheet2[CustomerID]), FILTER(ALLSELECTED(Sheet2), Sheet2[Date]=PreviousWeek))

By the way, we have no access to your sample data as you showed. You can also upload sample pbix to OneDrive (you can find it in following location) and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[mohittimpus]

Hi @v-xicai,

The measure does not work even with single selection of date. It should also work with time range and multiple selection of date. Please find the onedrive link for the file:

https://1drv.ms/u/s!AntMLanyDcxuaiesR6lsXng84Zk?e=cakl14

Thanks  

[amitchandak]

Hope you have created a date table using calendar. If not please make one.

Create a week behind measure

CALCULATE(COUNT(Sheet2[CustomerID]), dateadd(date[date],-7,DAY))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[mohittimpus]

Thanks  @amitchandak,

It work fine with date, I also have "Time Range" column, I also need to consider it, what changes I need to made in measure.

Please find the .pbix file attached:

https://drive.google.com/file/d/1e7uC84v0gjT6dYiOavrCnwhPCKQdXkHD/view?usp=sharing

Thanks