How to count rows upto user selected date?

[D_PBI]

Hi all,
I have a table named SALES which looks like the following:

I also have a date dimension table named dimDate that groups the dates by Year and by Quarter, as follows (I have shortened the table for example purposes):

There is a relationship between SALES[Sale Date] and dimDate[Date].
The is a slicer present on the table where the user can select the Quarters of interest (i.e. all four Quarters of 2000 AND/OR just Quarter 3 from 2003). In the below screenshot the grouping is done by myFinancial Year, however this shouldn't make a difference to the end result I'm seeking in post:

What I need to be able to do is count the number of Sale Ids from the earliest date in the SALES table upto (and including) the Quarters the users selects.
For example...
In the dummy data in the screenshots above, if the user selects the Quarters in the slicer visual then I would expect to the following result:

I'm not concerned if the DAX needed is created via a CalculatedTable or a Measure (if a measure then I'd bring in the Year and Quarter fields from the dimDate table, along with the Measure, into a single table visual).

I hope I've explained this clearly. It shouldn't be too troublesome but I'm not getting it right.
Thanks in advance.

[D_PBI]

Hi @rohit_singh, do you feel this is something you can help me with? Many thanks.

[rohit_singh]

Hi @D_PBI ,

I would just like some clarification since I'm seeing some discrepancy between the data and the expected output.

As per your sample data, you only have 3 rows for the year 2000, with 2 sale id's in Q1 and 1 in Q3.

However, your expected result has data in all 4 quarters for the year 2000

Could you please provide an expected output as per the sample data?

Kind regards,

Rohit

Appreciate your kudos! 😊

[D_PBI]

@rohit_singh  Hi Rohit,
As per my example data, if the user selects Quarters 1, 2, 3, 4 for the Year 2000, and also selects Quarter 3 for the Year 2003 - the output should be:
Year     Quarter     Count of Sales ID
2000    1               2 (count of dates 01/01/2000 and 10/01/2000)
2000    2               2 (count of dates 01/01/2000 and 10/01/2000)
2000    3               3 (count of dates 01/01/2000, 10/01/2000 and 01/08/2000)
2000    4               3 (count of dates 01/01/2000, 10/01/2000 and 01/08/2000)
2003    3               8 (count of dates 01/01/2000, 10/01/2000, 01/08/2000, 20/03/2001, 10/02/2002, 23/02/2002, 01/07/2002, 01/08/2003)

The sum for each selected Quarter needs to be a rolling total (so from the start of all Sales Dates upto (and including) the very last date of the selected Quarter.

I hope this explains things further. Thanks.

[rohit_singh]

Hi @D_PBI ,

Thanks for the clarification. That makes much more sense. You can try the steps below to see if it suits your requirements:

1) Add the Year and Quarter columns to your table from the date table (My date table is called dim_date)

Year = related(dim_date[Year])

Quarter = related(dim_date[Quarter])

2) Add a calculated column to compute max sale id for each year and quarter

Max Sale Id =

CALCULATE(

MAX('Quarterly Sales'[Sale Id]),

FILTER('Quarterly Sales',

'Quarterly Sales'[Year] = EARLIER('Quarterly Sales'[Year]) && 'Quarterly Sales'[Quarter] = EARLIER('Quarterly Sales'[Quarter])

)

)

3) Add a calculated column to compute no of rows for each year and quarter

Row Count =

COUNTROWS(

FILTER('Quarterly Sales',

'Quarterly Sales'[Year] = EARLIER('Quarterly Sales'[Year]) && 'Quarterly Sales'[Quarter] = EARLIER('Quarterly Sales'[Quarter])

)

)

4) Add a calculated column to compute quarterly for each year and quarter, and assign this value only to max sale id.

Quarterly Total =

if ('Quarterly Sales'[Sale Id] = 'Quarterly Sales'[Max Sale Id],

CALCULATE(

MAX('Quarterly Sales'[Row Count]),

FILTER('Quarterly Sales', 'Quarterly Sales'[Year] = EARLIER('Quarterly Sales'[Year]) && 'Quarterly Sales'[Quarter] = EARLIER('Quarterly Sales'[Quarter])))

, 0)

Your table will now look like this :

5) Next, you need to create a calculated table using your date table so that you can show all quarters.

Summarized Sales =

SUMMARIZE(

dim_date,

dim_date[Year],

dim_date[Quarter],

"Year-Qtr", dim_date[Year] * 100 + dim_date[Quarter],

"Num Sales", if (isblank(sum('Quarterly Sales'[Quarterly Total])), 0 , sum('Quarterly Sales'[Quarterly Total]))

)

6) Compute sales rank by month-quarter

Sales Rank =

 RANKX(

'Summarized Sales',

'Summarized Sales'[Year-Qtr],

,

ASC,

Dense

)

7) Finally, compute the running sales

Running Sales =

var _rank = 'Summarized Sales'[Sales Rank]

var _sum = CALCULATE(SUM('Summarized Sales'[Num Sales]), FILTER('Summarized Sales', 'Summarized Sales'[Sales Rank] <= _rank))

var _curr = 'Summarized Sales'[Num Sales]

return

_sum

This will give you the final output

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[D_PBI]

Thanks @rohit_singh  you have helped me grealty. I really appreciate it.
I have another ask for help. It's similar to the insight you've already helped me with but there is a slight difference.
You've already shown me how to count the Sale Ids (cumulative and quarter) but these Sale Ids were unique. There would be no duplicates in the Sale Id column regardless of the Sale Dates.

Now I need to count the Customers cumulatively (so from the start of time upto and including that quarter) and individually by quarter. The slight difference here is the Customer Id does appear more than once in the Customer Id column. Trying to re-use the code you've already provided, I was trying to find a way to de-duplicate the Customer Ids - are you able to help me to do this please?

For example, here is my starting Customer table:

My end result should be:

Just with the Sale example, there will be a separate dimDate table that the user is able to filter the Year and Quarter. However, based on the starting Customer table you will see the Customer Id is counted ONLY once for the cumulative column, and counted ONLY for the quarter column.
To give a further breakdown of what I'm aksing for:- The Y2004 Q4 has Customer Id 333 twice - it is not counted in the cumulative column as Customer Id 333 is counted in Y2000 Q3.  Also, the Customer Id 333 is only counted once in the Quarter column for Y2004 Q4 even thought it appears twice - this is because we're looking to count only the distinct Customer Ids.

I hope this makes sense. Are you able to help me, please? Thanks.

[D_PBI]

Hi @rohit_singh 

This is just a courtesy response to say 'thanks' for your efforts. I am implementing it now and will test. I'll come back with the conclusion, shortly.