cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sharifahy
Helper II
Helper II

DISTINCTCOUNT of items and sum by previous month

Hi,

 

I want to create a DAX that can count the total of items and sum it by the previous month. As an example,

 

ItemIDReceiveDate
A101/01/2020
A201/02/2020
A101/02/2020
B301/03/2020

 

Count of items and sum it by prev month,, (Jan, Jan+Feb, Jan+Mar,etc.) without creating a new table. I just need the CumulativeItemsCount field. ItemsCount is just a sample.

 

ReceiveDateMonthItemsCountCumulativeItemsCount
Jan11
Feb23
Mar14

 

Do I need to create a column that has the rank of the items filtered by the year and month before proceed with the CumulativeItemsCount column?

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@sharifahy , Create count measure

Count Item = count(Table[ItemID])

or
Count Item = distinctcount(Table[ItemID])

Then create a cumulative like

CumulativeItemsCount = calculate([Count Item], filter(allselected(Table]), Table[Date] <= Max(Table[Date])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

1 REPLY 1
amitchandak
Super User IV
Super User IV

@sharifahy , Create count measure

Count Item = count(Table[ItemID])

or
Count Item = distinctcount(Table[ItemID])

Then create a cumulative like

CumulativeItemsCount = calculate([Count Item], filter(allselected(Table]), Table[Date] <= Max(Table[Date])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

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

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors