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

[Anonymous]

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

[dax · Accepted solution]

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.

[Anonymous]

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

[Anonymous]

Wow.. Thanks a lot..!! 🙂