Showing bar chart timeline for current date +/- 7 days

[C-F-G]

Hello experts,

I need your help again.

I'm working with a given data model on a company analysis server. There is an order table with a due date for every order ("t_sla_ende"). The table is filtered by page filters and slicers to show the specific open orders. I want to show the user a bar chart with the amount of open orders per due date. I choose "t_sla_ende" for the x-axis and count of "t_sla_ende" for the y-axis. The bar chart visual works as expected. 

Because the oldest due dates are from 2021 and newest due dates are in 2025, so I want show show the user only a section from the x-axis with the current dates (from today +/- 7 days) and I want to sum all orders which are outside of date range. (open orders with due date more than 7 days in the past or more than 7 days in the future should be summed on top of the first and last shown date). Basically this concept works well - see my screenshot. The blue bars with the count of open orders per due date is correct.

But(!) the sum of older or younger orders outside the date range (the 2 orange bars) are very wrong and I couldn't find the reason.

You can see it on the right orange bar - it should count all orders which are due after the 17th December. Only by looking at the picture you see this should be much more. 

Total orders are 4743, before date range are 260 (calculated 352) , inside the range are 2374, after 17th Dec are 2074 (calculated 371), with blank dates are 35.

The counting is made by 2 measures:

Count before Range = IF([SLA_Monitor is first day in range] = 1,COUNTAX(Auftrag,Auftrag[t_sla_ende]<SELECTEDVALUE(Auftrag[t_sla_ende])))

Count after Range = IF([SLA_Monitor is last day in range]=1,COUNTAX(Auftrag,Auftrag[t_sla_ende]>SELECTEDVALUE(Auftrag[t_sla_ende])))

 Do you see why the measures are counting the orders wrong?

Help is very appreciated.

[C-F-G]

Meanwhile I found a solution by changing my measures. 

The key was changing from count() to calculate() with a proper filter statement. 

Thank you and have nice weekend...

Count before Range = IF([SLA_Monitor is first day in range]=1,CALCULATE([Anz Auftrag],FILTER(ALL(Auftrag[t_sla_ende]),Auftrag[t_sla_ende]<[SLA_Monitor first date in range])))

Count after Range = IF([SLA_Monitor is last day in range]=1,CALCULATE([Anz Auftrag],FILTER(ALL(Auftrag[t_sla_ende]),Auftrag[t_sla_ende]>[SLA_Monitor last date in range]))) 

[v-zhangtin-msft]

Hi, @C-F-G 

Can you provide example files? Please remove any sensitive data in advance.

How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[C-F-G]

Hello charlotte,

thank you for your help. I provided a sample file here - https://drive.google.com/file/d/1nZqB46od9z5d9fdAP3vhWXCNSm8zRiF6/view?usp=sharing 

After some more hours I think that my problem is rather fundamental. 

When showing the amount of open orders in a chart then the measure which calculates the values is always evaluated in the context of the respective date shown on the x-axis. (aka selectedvalue?)

Any ideas, how can I solve this?

Thank you

[v-zhangtin-msft · Accepted solution]

Hi, @C-F-G 

Failed to open the link you provided. Can you provide the data in a different way? Simulating some of the data into Excel would be fine.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.