Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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.

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. 

Need Help
  • Fowmy's avatar
    Fowmy
    5 years ago

    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

2 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      Fowmy 

       

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

       

      thank you a lot!