Filtering CALCULATE by dates (months)

[Anonymous]

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.

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. 

[Fowmy · Accepted solution]

@Anonymous 

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

[Anonymous]

@Fowmy 

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

thank you a lot!