cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Filtering CALCULATE by dates (months)

Hi,

 

I'm trying to build up very simply monthly trend within year(2020), but on the visualized chart all 12 months + Empty month. 

 

I have got 3 tables: 

1. Calendar. Calendar  = CALENDAR("01.01.2020";max('service_level'[Receivied on])) -meaning till 18.09.2020

2. service level table which contains order receiving date and  order # - the last date in that table is 18.09.2020

3. claims table which contains claim receiving date and order # - the last date in that table is 16.09.2020

 

tables 2 and 3 are linked to Calendar.

service level % is calculating by measure:

service%=CALCULATE(1-[amount of confirmed claims] / [total amount of orders];filter('Calendar';'Calendar'[Date]<=max('service_level'[Received on])))

 

but graphs is ...it shouldn't visualize Empty, Oct, Nov, and December.

chart.jpg

Please advice how to solve, i have tried many variants, but i couldnt reach success:(( and briefly explain why? i have some in mind that "1-.." is impact on it. if i skip "1-..." in the formula - everything is ok at chart, but not a correct calculation of service%.

Thank you in advance. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

@SlavaKep 

Can you try this measure:

service% = 
CALCULATE (
    DIVIDE(
        [total amount of orders]- [amount of confirmed claims],
        [total amount of orders]
    );
    FILTER ( 'Calendar'; 'Calendar'[Date] <= MAX ( 'service_level'[Received on] ) )
)

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

View solution in original post

2 REPLIES 2
Highlighted
Community Champion
Community Champion

@SlavaKep 

Can you try this measure:

service% = 
CALCULATE (
    DIVIDE(
        [total amount of orders]- [amount of confirmed claims],
        [total amount of orders]
    );
    FILTER ( 'Calendar'; 'Calendar'[Date] <= MAX ( 'service_level'[Received on] ) )
)

 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

View solution in original post

Highlighted

@Fowmy 

 

well, i spent whole saturday and part of today, to fix, but you saved my rest of Sunday:)

 

thank you a lot!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors