calculate counts for dates and show them in a bar chart

[Anonymous]

Hello everybody,

I am trying to count how many rows I have on a table with the correspondent date <= that the date in the filter. Then I want to show this in a bar chart grouped by ageing.

So in summary I have a table like the following:

ID    START_DATE     AGEING

1       01/12/2017    DATEDIFF('TABLE'[STOCK_START_DATE];today();DAY

2       2/02/2018

3       2/02/2018

4       2/03/2018

5       4/04/2018

6       4/05/2018

What I am trying to do is: filter april 2018 and have a count of how many rows I have in the table before that date and then have a bar chart that shows the values grouped by age. At the moment if I filter I can only see in the x axis the ageing related to the month of the filter (so if I select april I will only see the ageing calcalutated for the dates that have april as months)

can you please help me?

thank you a lot,

Angela

[v-frfei-msft · Accepted solution]

Hi @Anonymous,

I made one sample for your reference. Please check the step as below, if it doesn't meet your requirement, kindly share your excepted result to me.

1. Create a date table and new a calculated column in the new table.

date = CALENDARAUTO()

YearMonth = YEAR('date'[Date]) & "-"& FORMAT('date'[Date],"mmm")

2. Create the measures as below.

ageing = DATEDIFF(MAX(Table1[START_DATE]),TODAY(),DAY)

countrows = 
var maxdate = CALCULATE(MIN('date'[Date]),ALLSELECTED('date'[Date]))
return
CALCULATE(COUNTROWS(Table1),FILTER(Table1,Table1[START_DATE]<maxdate))

Measure = var maxdate = CALCULATE(MIN('date'[Date]),ALLSELECTED('date'[Date]))
return
IF(MAX(Table1[START_DATE])<maxdate,1,0)

3. Create bar chart and filter the bar chart using the Measure.

For more details, please check the pbix as attached.

Regards,

Frank

[v-frfei-msft]

Hi @Anonymous,

Does that make sense? If so, kindly mark my answer as a solution to close the case.

Regards,
Frank

[PattemManohar]

@Anonymous Please try this using "New Measure"

Test1 = COUNTROWS(FILTER(_DimDate,_DimDate[Date]<=_DimDate[Date]))