cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
CR Member
Member

Cumulative count not working

Hi !

I'm lost because, usually, it works fine when I create a measure to cumulate the values by month. Today, no...

 

Here is a part of my Excel table:

 

Actual typeTagName ValidationDate PurposeDate
PackageNot ValidatedCREATION21/09/2018
PackageNot ValidatedCREATION21/09/2018
PackageNot ValidatedCREATION21/09/2018
PackageNot ValidatedCREATION22/10/2018
PackageNot ValidatedCREATION22/10/2018
PackageNot ValidatedEND07/01/2019
PackageNot ValidatedEND07/01/2019
PackageNot ValidatedEND07/01/2019

 

Now the measure:

 
Cumul Created Date TEST = 
    CALCULATE(COUNT('MTI test'[Actual type]);
 FILTER(ALLSELECTED('MTI test');'MTI test'[TagName Validation]="Not Validated"); FILTER(ALLSELECTED('MTI test');'MTI test'[Actual type]="Package"); FILTER(ALLSELECTED('MTI test');'MTI test'[Date]<=MAX('MTI test'[Date]) ))
 
But the visual is not what I want:
 
Actual type2018-092018-102018-122019-012019-02
Electrical  5  
HVAC   8 
INSTRUM   88
LOOP  588
Package35 8 
Total  588

 

The Value with the TagName End is counted despite of the filter made on CREATION DATE and other Actual type are counted as well (propbably because they got a value in the same month as Package but it should not be taken into account)

 

Regards,

CR

 
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Cumulative count not working

hi, @CR 

Add a date table and then create the relationship with "MTI test" [Date]

Then adjust your formula as below:

Measure = 
    CALCULATE(COUNT('MTI test'[Actual type]),
                FILTER('MTI test','MTI test'[TagName Validation]="Not Validated"),
                FILTER('MTI test','MTI test'[Actual type]="Package"),
                FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date])
	))

Then right-click [Actual type] field and select "Show items with no data"

1.JPG

 

My simple sample result:

2.JPG

Best Regards,

Lin

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

Re: Cumulative count not working

@CR is this what you are looking for?

 

image.png





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

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




CR Member
Member

Re: Cumulative count not working

Hi @parry2k 

 

I need to display this:

 

 
Actual type2018-092018-102018-122019-012019-02
Electrical     
HVAC     
INSTRUM     
LOOP     
Package35555
Total     

 

I just need to cumulate the CREATION DATE values without taking the END into account, and with cumulative data in the months coming after...

 

Regards,

CR

 

Super User
Super User

Re: Cumulative count not working

@CR in that case create a date dimension table in your model and set relation  between createion date and date table, and update formula to use date from date dimension and that will do the job. 





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

Proud to be a Datanaut! Kudos Help Smiley Happy
Connect with me on Linkedin. Feel free to email me with any of your BI needs.




 




Community Support Team
Community Support Team

Re: Cumulative count not working

hi, @CR 

Add a date table and then create the relationship with "MTI test" [Date]

Then adjust your formula as below:

Measure = 
    CALCULATE(COUNT('MTI test'[Actual type]),
                FILTER('MTI test','MTI test'[TagName Validation]="Not Validated"),
                FILTER('MTI test','MTI test'[Actual type]="Package"),
                FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date])
	))

Then right-click [Actual type] field and select "Show items with no data"

1.JPG

 

My simple sample result:

2.JPG

Best Regards,

Lin

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