Forum Discussion

D_PBI's avatar
D_PBI
Icon for Post Partisan rankPost Partisan
4 years ago

How to count rows upto user selected date?

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.



Need Help

6 Replies

  • rohit_singh's avatar
    rohit_singh
    Icon for Solution Sage rankSolution Sage
    4 years ago

    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's avatar
      D_PBI
      Icon for Post Partisan rankPost Partisan
      4 years ago

      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's avatar
        rohit_singh
        Icon for Solution Sage rankSolution Sage
        4 years ago

        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! ðŸ˜Š