cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PS
Helper I
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
dax
Community Support
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] ) )
        )
    )

253.PNG254.PNG

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.

 

View solution in original post

3 REPLIES 3
dax
Community Support
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] ) )
        )
    )

253.PNG254.PNG

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.

 

View solution in original post

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

Wow.. Thanks a lot..!! 🙂

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

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.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.