cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Measure that counts a category if the count of the category changes over time

Below is an example of the matrix that counts the category:

Below is an example of the output from the measure I need:

 Category 2022.24 2022.25 2022.26 BCP-06 0 0 1 BCP-08 0 0 0
1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous

Create a measure like this

``````Measure =
VAR _DATE=CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Category]),[Date]<MAX('Table'[Date])))
VAR _VALUE=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Category]=MAX('Table'[Category])&&[Date]=_DATE))
RETURN IF(MAX('Table'[Date])<>CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Category]))&& _VALUE<>SUM('Table'[Value]),1,0)``````

Then add the measure in Matrix visual .

I have attached my pbix file , you can refer to it .

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

2 REPLIES 2
Community Support

Hi @Anonymous

Create a measure like this

``````Measure =
VAR _DATE=CALCULATE(MAX('Table'[Date]),FILTER(ALLEXCEPT('Table','Table'[Category]),[Date]<MAX('Table'[Date])))
VAR _VALUE=CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Category]=MAX('Table'[Category])&&[Date]=_DATE))
RETURN IF(MAX('Table'[Date])<>CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Category]))&& _VALUE<>SUM('Table'[Value]),1,0)``````

Then add the measure in Matrix visual .

I have attached my pbix file , you can refer to it .

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

Solution Sage