Forum Discussion

btalahmbong's avatar
btalahmbong
Advocate II
9 years ago
Solved

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!

Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    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.

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    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