Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
CR
Resolver II
Resolver II

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
v-lili6-msft
Community Support
Community Support

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.

View solution in original post

4 REPLIES 4
v-lili6-msft
Community Support
Community Support

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.
parry2k
Super User
Super User

@CR is this what you are looking for?

 

image.png



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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

 

@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. 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.