Count numbers of rows in a table with dates

[SebaSpotti]

Hi guys,

I have a table, Table1, with a column of dates, Table1[COMP_DATE]. The dates in Table1[COMP_DATE] can be repetead and they are unsorted.

For each row, I want to calculate how many rows there are in the table with Table1[COMP_DATE] between the date in the row and an year before. Something like:

Table.RowCount(Table1[COMP_DATE]>Table1[COMP_DATE](x)-1 year and Table1[COMP_DATE]<=Table1[COMP_DATE](x)).

any suggestions?

[Kedar_Pande · Accepted solution]

@SebaSpotti 

Create a calculated column with the following DAX expression:

CountLastYear = 
VAR CurrentDate = Table1[COMP_DATE]
RETURN
COUNTROWS(
FILTER(
Table1,
Table1[COMP_DATE] > EDATE(CurrentDate, -12) &&
Table1[COMP_DATE] <= CurrentDate
)
)

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[SebaSpotti]

It works. But I have a prolem. I need to filter to select a date in a slicer, so i need to remove that filters and still had to work

[Sahir_Maharaj]

Hello @SebaSpotti,

Can you please try the following approach:

RowsInLastYear = 
VAR CurrentDate = Table1[COMP_DATE]
RETURN
    COUNTROWS(
        FILTER(
            Table1,
            Table1[COMP_DATE] > (CurrentDate - 365) && 
            Table1[COMP_DATE] <= CurrentDate
        )
    )

Hope this helps.

[SebaSpotti]

It works. But I have a prolem. I need to filter to select a date in a slicer, so i need to remove that filters and still had to work

[Anonymous]

Hi @SebaSpotti ,

Could you add more details on where you're stuck now? From the discussion above you now get a new calculated column, what is your expected output next?

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum