cancel
Showing results for
Search instead 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.?

Thanks in advance.

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

How can i achieve this. Please help.

Thanks in advance.

-Paurnima
Helper I

Wow.. Thanks a lot..!! 🙂

Helpful resources

Announcements

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

Check it Out!

Click here to read more about the March 2021 Updates!

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors