cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

## Count Text Values by Specific Date

Is it possible to count a set of rows for a set of dates, then show that sum on a visual as the total on the last date(only last date showed) ?
For Example;

We have a table with 2 columns Date and Title:

DATE                    TITLE

1/1/2016              Charles Dickens

1/2/2017              William Shakespeare

1/3/2017              Numen Georges

I want to be able to have a visual that returns the sum as follows;

DATE                   SALES

1/3/2017             3

So in a bar chart or waterfall chart, I'll have a single bar with only one date and the sum of the counted rows up to that date.

Thanks for the help!

1 ACCEPTED SOLUTION
Microsoft

Hi @btalahmbong,

I reproduce your scenario using the sample data. Create two measures using the fomulas.

```The last day = CALCULATE(MAX(Test[DATE]),ALL(Test))
SALES = CALCULATE(COUNTA(Test[TITLE]),FILTER(Test,Test[DATE]<=Test[The last day]))```

Create a table visual, select two measures as value level, you will get the expected result as follows.

Thanks,
Angelia

2 REPLIES 2
Microsoft

Hi @btalahmbong,

I reproduce your scenario using the sample data. Create two measures using the fomulas.

```The last day = CALCULATE(MAX(Test[DATE]),ALL(Test))
SALES = CALCULATE(COUNTA(Test[TITLE]),FILTER(Test,Test[DATE]<=Test[The last day]))```

Create a table visual, select two measures as value level, you will get the expected result as follows.

Thanks,
Angelia

Super User

Create a couple measures like:

```MaxDate = MAX(Table[DATE])
Total Count = RETURN CALCULATE(COUNTA(Table[TITLE]),FILTER(Table[DATE]<=[MaxDate]))```

Then put Total Count in a Bar Chart and put a Card visual under it with MaxDate. That's the easiest way.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors