topic Re: Measure that calculates the count of closed actions in the last three months in DAX Commands and Tips

Measure that calculates the count of closed actions in the last three months

Sonnet — Sun, 19 May 2024 18:43:45 GMT

Hello everyone, I have a fact table, namely the Action Table, which has a Closed date linked to the calendar date via an inactive relationship.

I am seeking to create a measure that computes the count of actions that were closed within the last three months from today’s date.

Subsequently, I want to use this measure to draw a comparison with the count of actions from the preceding three months.

For instance:

The first measure counts the actions closed from 19/05/2024 to 19/02/2024.

Then, I need a second measure to use in a KPI visual as the target measure, to calculate the count of closed actions in three months previously to the three months in the first measure, in our case, from 18/02/2024 to 18/11/2023.

Another option would be to calculate the count of closed actions in the last 90 days compared to the count of closed action between 91 and 180 days.

Here is the measure for closed actions without any specified dates:

Total Completed = CALCULATE(COUNT('Actions'[Date Closed]), 'Actions'[Date Closed]<>BLANK(), USERELATIONSHIP('Calendar'[Date],'Actions'[Date Closed]))+0

Could anyone guide me on how to accomplish this? Thanks in advance!

Action Id	Date Closed
1	20/03/2023
2	15/06/2023
3
4	02/09/2023
5	23/12/2023
6
7	10/02/2024
8	23/04/2024
9	19/05/2024

Re: Measure that calculates the count of closed actions in the last three months

Greg_Deckler — Sun, 19 May 2024 11:40:33 GMT

Sonnet Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

Re: Measure that calculates the count of closed actions in the last three months

Sonnet — Sun, 19 May 2024 22:44:50 GMT

I've managed to create the measures:

Last 3 months =

CALCULATE(COUNTROWS('Actions'),FILTER('Actions', 'Actions'[Date Closed]>TODAY()-90

&& 'Actions'[Date Closed]<=TODAY() &&'Actions'[Date Closed]<>BLANK() ))+0

3 months before [Last 3 months] =

CALCULATE(COUNTROWS('Actions'),FILTER('Actions', 'Actions'[Date Closed]>TODAY()-180

&& 'Actions'[Date Closed]<=TODAY()-91 &&'Actions'[Date Closed]<>BLANK()))+0

The results are es expected when used in a card visual. If I used them in a KPI visual with [Last 3 months] at Value, Month and Year column from the calendar table as Trend Axis, and 3 months before [Last 3 months] at the Target, all I get is 0, which is not expected.

Do you know why?

Re: Measure that calculates the count of closed actions in the last three months

Sonnet — Mon, 20 May 2024 09:03:17 GMT

any help is much appreaciated.