Forum Discussion

jseow's avatar
jseow
Frequent Visitor
8 years ago
Solved

Cumulative Count by Date With Filter

Hi all,

 

I'm not sure that I've properly described my issues in the subject line, but essentially what I have are two tables, Products and Reviews, joined on a Product ID, with the Reviews also having a Date field for the review creation.

 

What I am trying to get, is a chart that shows over time (month on x axis), how many products have at least one review as of end of that month.

 

In my attached data set (OneDrive Link https://1drv.ms/u/s!AuOd65xYgFuGaXlqQEWn2NbVs1E), this would be 1 product in January, 2 products in February/March, and then 3 products for the remainder of the year. 

 

Something I can do really easily in Excel, but struggling on how to do this in PBI Desktop, any help would be appreciated!

 

Thanks!

Need Help

2 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi jseow，

    Please create a measure using the following formula.

    COUNT =
    CALCULATE (
     DISTINCTCOUNT ( Reviews[Product ID] ),
     FILTER (
     ALL ( Reviews ),
     Reviews[Review ID] > 0
     && Reviews[Date] <= MAX ( Reviews[Date] )
     )
    )


    Then add the measure as value in your bar chart, you will get expected result.



    Please download the .pbix file for more details.

    Best Regards,
    Angelia

    • jseow's avatar
      jseow
      Frequent Visitor
      8 years ago

      Thanks so much, that's perfect!