Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Alejandra Avila]

Hello! 

Hope someone can help me with this I have been trying to create a measure that adds the monthly result of another calculated measure which uses COUNTROWS but I’m not being able to get the desired result, my measures are defined as follows:

 

Reached Customers = COUNTROWS(FILTER(VALUES(‘Monthly'[Customers]),AND(‘Monthly'[PY Salesl]>1,[TY Sales]>1)))

Cumm Reached Customers =

VAR EarliestDate = CALCULATE(MIN(Calendar[Month]),ALLSELECTED())
RETURN CALCULATE([Reached Customers],FILTER(ALL(CCalendar[Month]),Calendar[Month]<= MAX(Calendar[Month]) && Calendar[Month]>= EarliestDate))

 

With this I get the following result:

 

Month	Reached Customers	Cumm Reached Customers	DESIRED Result
01-Jan-17	22	22	22
01-Feb-17	23	34	56
01-Mar-17	32	47	103
01-Apr-17	27	51	154
01-May-17	32	55	209
01-Jun-17	30	59	268

 

As you see the Cumm Reached measure is only adding the different customers from each month, however what I need is for it to add month by month result (as shown in column DESIRED Result), I understand this is not working because my original measure “New Customers” is using COUNTROWS and VALUES thus filtering only distinct values, which is actually what I need for that measure, the problem comes when trying to add those values. 

Please help! 

Thanks!

Ale 

——————————
Alejandra Avila
Store Intelligence
México
5533310967
——————————

[Hasham Niaz]

Hi,

Please try to correct your DAX using below articles;

https://community.powerbi.com/t5/Quick-Measures-Gallery/Running-total/td-p/218593

https://www.mssqltips.com/sqlservertip/4841/calculating-mtd-qtd-ytd-running-and-cumulative-total-in-power-bi/

You can easily find DAX solution to Running Total problem

——————————
Hasham Niaz
Sr. BI Consultant
Karachi, Pakistan
——————————
——————————————-

[Kevin Zandee]

It appears you are trying to use month instead of date try something like below

CALCULATE([Reached Customer],FILTER(ALLSELECTED(Cal), Cal[Date]<= MAX(Cal[Date])))

——————————
Kevin Zandee
Consultant
GT Services
Calgary AB
4038806941
——————————
——————————————-