Forum Discussion
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 type | TagName Validation | Date Purpose | Date |
| Package | Not Validated | CREATION | 21/09/2018 |
| Package | Not Validated | CREATION | 21/09/2018 |
| Package | Not Validated | CREATION | 21/09/2018 |
| Package | Not Validated | CREATION | 22/10/2018 |
| Package | Not Validated | CREATION | 22/10/2018 |
| Package | Not Validated | END | 07/01/2019 |
| Package | Not Validated | END | 07/01/2019 |
| Package | Not Validated | END | 07/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])
))| Actual type | 2018-09 | 2018-10 | 2018-12 | 2019-01 | 2019-02 |
| Electrical | 5 | ||||
| HVAC | 8 | ||||
| INSTRUM | 8 | 8 | |||
| LOOP | 5 | 8 | 8 | ||
| Package | 3 | 5 | 8 | ||
| Total | 5 | 8 | 8 |
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
hi, Anonymous
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"
My simple sample result:
Best Regards,
Lin
4 Replies
hi, Anonymous
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"
My simple sample result:
Best Regards,
Lin
Anonymous is this what you are looking for?
Hi parry2k
I need to display this:
Actual type 2018-09 2018-10 2018-12 2019-01 2019-02 Electrical HVAC INSTRUM LOOP Package 3 5 5 5 5 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
Anonymous 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.
