topic Re: Calculate no of customer until a selection is made in the filter in DAX Commands and Tips

Calculate no of customer until a selection is made in the filter

mottya — Thu, 11 Nov 2021 07:56:47 GMT

I have a delivery table that includes the customer ID and the delivery dates. It's connected to another table that contains the customer ID and the first date of delivery.

I want to know how many customers I have until a date I select in the filter (qtr or specific month)

for example, if I have three new customers on July 21 and 4 on August 21 and I choose September 21 (or Q3), I would like to see that I have seven customers

I tried some ways but failed

I hope someone can help me with that

Thanks

Re: Calculate no of customer until a selection is made in the filter

Greg_Deckler — Thu, 11 Nov 2021 09:27:48 GMT

mottya Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Calculate no of customer until a selection is made in the filter

v-robertq-msft — Mon, 15 Nov 2021 03:04:39 GMT

Hi,

According to your description, I can roughly understand your requirement, I think you can create a calendar table and try this measure:

This is my test data:

You can first create a calendar table like this:

calendar = CALENDARAUTO()

Then make sure it doesn’t have a relationship with the main table, then create a measure like this:

Customer count = CALCULATE(distinctCOUNT('delivery table'[Customer ID]),FILTER(ALL('delivery table'),[delivery date]<SELECTEDVALUE('calendar'[Date])))

And you can create slicer a measure to palce the date column in the calendar table and a card chart to place this measure, you can get what you want:

You can download my test pbix file below

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

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