cancel
Showing results for
Did you mean:
Helper I

## Measure to display latest/last month data based on selected Date range in Date Filter

Hi Team,

I am working on training data Dashboard. I have to disply the number of users "certified/outstanding/inprogress" to complete the training monthly or quarterly.

I am using pie chart to display the % completion based on status(certified/outstanding/inprogress).

Now,want to create a measure which will display Latest(last) months data if i select a Date Range in a filter. Also, if I select the Quarter it should show the Last month of User count in that selected Quarter.

For Eg. 1) If I select Q1 and if the quarter is completed it will display "March" users count.

2) If quarter is incomplete or if I select a date range from Jan-June 2019 it should display the latest user count in the pie chart. i.e June months user count based on their completion status.

Is it possible.?

-Paurnima

1 ACCEPTED SOLUTION
Community Support

Hi PS,

Based on my test, I find that if you use multiple slicers or filter, it will apply "AND" logic instead of "OR" logic, so when  you use Q1 and date range is jan-june, it will calculate based on Q1.

Below is my sample and result.

datename

 1/1/2019 a 1/1/2019 b 1/1/2019 c 1/5/2019 a 1/5/2019 a 3/2/2019 c 3/5/2019 b 4/1/2019 c 5/1/2019 c 6/1/2019 c 6/1/2019 b 7/1/2019 b 7/5/2019 a 8/15/2019 b 8/20/2019 c 8/23/2019 a 3/2/2019 a 3/5/2019 b 4/1/2019 c 5/1/2019 c 6/1/2019 a 6/1/2019 a 7/1/2019 b 7/5/2019 b 8/15/2019 c 8/20/2019 c 8/23/2019 a 3/2/2019 b 8/12/2019 a 8/20/2019 a 8/23/2019 b

create a calendar table by measure like below and create relationship between these table based on date

`calendar = CALENDAR(DATE(2019,1,1),DATE(2019,8,31))`

create measure like below

```Measure 3 =
CALCULATE (
COUNT ( 'Table'[name] ),
FILTER (
( 'Table' ),
MONTH ( 'Table'[date] ) = MONTH ( MAX ( 'calendar'[Date] ) )
)
)
/ CALCULATE (
COUNT ( 'Table'[name] ),
FILTER (
ALL ( 'Table' ),
MONTH ( 'Table'[date] ) = MONTH ( MAX ( 'calendar'[Date] ) )
)
)
```

Best Regards,
Zoe Zhi

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

3 REPLIES 3
Community Support

Hi PS,

Based on my test, I find that if you use multiple slicers or filter, it will apply "AND" logic instead of "OR" logic, so when  you use Q1 and date range is jan-june, it will calculate based on Q1.

Below is my sample and result.

datename

 1/1/2019 a 1/1/2019 b 1/1/2019 c 1/5/2019 a 1/5/2019 a 3/2/2019 c 3/5/2019 b 4/1/2019 c 5/1/2019 c 6/1/2019 c 6/1/2019 b 7/1/2019 b 7/5/2019 a 8/15/2019 b 8/20/2019 c 8/23/2019 a 3/2/2019 a 3/5/2019 b 4/1/2019 c 5/1/2019 c 6/1/2019 a 6/1/2019 a 7/1/2019 b 7/5/2019 b 8/15/2019 c 8/20/2019 c 8/23/2019 a 3/2/2019 b 8/12/2019 a 8/20/2019 a 8/23/2019 b

create a calendar table by measure like below and create relationship between these table based on date

`calendar = CALENDAR(DATE(2019,1,1),DATE(2019,8,31))`

create measure like below

```Measure 3 =
CALCULATE (
COUNT ( 'Table'[name] ),
FILTER (
( 'Table' ),
MONTH ( 'Table'[date] ) = MONTH ( MAX ( 'calendar'[Date] ) )
)
)
/ CALCULATE (
COUNT ( 'Table'[name] ),
FILTER (
ALL ( 'Table' ),
MONTH ( 'Table'[date] ) = MONTH ( MAX ( 'calendar'[Date] ) )
)
)
```

Best Regards,
Zoe Zhi

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

Helper I

Hi Team,

I have query on the same dashboard.

Now I am calculating % of employees Completed the Training and using following Measure which giving me the desire output.

All Completion % = CALCULATE(COUNT('Summary Training Completion'[User ID]),FILTER('Summary Training Completion',[Total_Status]="Certified"))/(COUNT('Summary Training Completion'[User ID]))

But now customer requirement is if there are no employees enrolled for the training in any specific month it should display completion as 100% for that month. Instead of displaying Blank in Card visual or Line Chart.

-Paurnima
Helper I

Wow.. Thanks a lot..!! 🙂

Announcements