Calendar data from incorrect monthly view to weekly view

[Anonymous]

Hello community,

I have a report that has data from an excel file. Each line is an issues, with both a "date opened" and "date closed". I would like to see a graph on a weekly basis what the status on the issues are. An issue is opened on a specific week, if its "date opened" is before (or during) the week in question AND "date closed" is after the week in question OR still blank (still open).

The formula I am using right now looks like this: 

Open Issues = CALCULATE(COUNTX(FILTER(AllIssues,AllIssues[Reported]<=max(G_CALENDAR[DATE_ID]) && (ISBLANK(AllIssues[Fixed]) || AllIssues[Fixed]>max(G_CALENDAR[DATE_ID]))),(AllIssues[Machine])),CROSSFILTER(AllIssues[Reported],G_CALENDAR[DATE_ID],None))

The result is the following:

A graph on a weekly basis, which counts incorrectly and for some reason only shows the first weeks situation of the month.

Any ideas on how to fix this?

[Anonymous]

Hi @Anonymous ,

I'm not quite sure if I understand your needs accurately.
Sample data without sensitive information and expected output would help tremendously.

Please see this post regarding:  How to provide sample data in the Power BI Forum 

Hope it helps,

Community Support Team _ Caitlyn

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

[Anonymous]

@Anonymous any suggestions how to solve my problem?

[Anonymous]

Hello @Anonymous !

Here is some sample data with sensitive data removed: https://we.tl/t-tmP2SxgUF8 

So I would like to have a graph, showing the issues open on a weekly basis. If an issues is open on a specific week, depends on the "reported" and "fixed" dates, in case the issue has been open during that week, is should count it to the issues open that week. The different colour is my first message is the "criticality" seen in the excel as well. Also there is the location of the machine and of course the machine in question.
If I'm still unclear, please tell and I will try to clarify.
Kr,

Ossian

[amitchandak]

@Anonymous , refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785