Reply
Highlighted
Frequent Visitor
Posts: 6
Registered: ‎01-18-2019
Accepted Solution

MTD cumulative count on the number of dates for each month

I need a count of the number of dates in column 'CWOrdered' where date is <= to the data point (date axis) in a line graph to get a cumulative graph or MTD. I have a separate date table called 'Date' for the timeline in my graphs.

 

CwOrdered     MTD
2016-01-05     1
2016-01-15     2
2016-02-24     3
2016-03-20     4
2016-04-10     5
2016-05-35     6
etc. so that January 2016 = 2 February 2016 = 3 and so forth

 

 

I'm guessing it could be done with a simple count on the number of dates of the coulumn until the date where it is displayed in the line graph but I can't seem to get it working.

If I just add it to the graph I get the count of 'CWOrdered' as a straight line in the graph which isn't very helpful =)


Accepted Solutions
Super User
Posts: 10,536
Registered: ‎07-11-2015

Re: MTD cumulative count on the number of dates for each month

Should be something along the lines of:

 

Measure = 
VAR __maxDate = MAX('Calendar'[Date])
VAR __table = FILTER(ALL('Table'),[CwOrdered]<=__maxDate)
RETURN
COUNTX(__table,[CwOrdered])

Alos, check out my Time Intelligence the Hard Way:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


View solution in original post

Community Support Team
Posts: 7,656
Registered: ‎05-02-2017

Re: MTD cumulative count on the number of dates for each month

Hi @The_BI_Noob,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post


All Replies
Super User
Posts: 10,536
Registered: ‎07-11-2015

Re: MTD cumulative count on the number of dates for each month

Should be something along the lines of:

 

Measure = 
VAR __maxDate = MAX('Calendar'[Date])
VAR __table = FILTER(ALL('Table'),[CwOrdered]<=__maxDate)
RETURN
COUNTX(__table,[CwOrdered])

Alos, check out my Time Intelligence the Hard Way:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Community Support Team
Posts: 7,656
Registered: ‎05-02-2017

Re: MTD cumulative count on the number of dates for each month

Hi @The_BI_Noob,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.