cancel
Showing results for
Did you mean:
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.

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%.

1 ACCEPTED SOLUTION

Accepted Solutions
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 🙂

2 REPLIES 2
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 🙂

Frequent Visitor

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

thank you a lot!

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Power Platform October Community Highlights

Check out the top community contributors across all of the communities

#### 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