Display Total Count of Work Lots To-Date from the beginning of the project till Last Wk & Last Mth.

[Anonymous]

Hi Community,

We are reporting on Aconex data in Azure using Power BI.

Project Live To-Date visual in the first screenshot is showing correct numbers.

The business wants to see numbers for Last Week (Count right from the beginning of the project till Last Week) and Last Month (Count right from the beginning of the project till Last Month).

Business is doing this report currently in Excel. They are running this report every Week and every Month and are deducting the numbers from 'Project Live To-Date' to generate reports for Last Month and/or Last Week which is a very tedious process.

I have done the data modeling but when I am doing a simple filter based on the business logic/conditions, the data view is not showing which obviously is demanding me to use DAX.

I am now confused as to spend time and re-model the data model so it will show the data for the required logic or should I just do DAX.

Can anyone please suggest which is the best way forward? And if it is DAX, can anyone please give me some steps to implement DAX successfully. This is going to be a Bar Chart in Power BI.

The logic I got for both the bars is as below:

Last Week:

Show 2 Bars side by side by Zone.

Bar1 - Count of Open (From Work Lot Status Column ) Work Lots in the Last 7 Days (From Work Lot Opened Date (Actual) Column)

Bar2 - Count of Completed (From Work Lot Status Column) & Compliant (From Work Lot Compliance Column) in the Last 7 Days (From Work Lot Closed Date (Actual) Column)

Last Month:

Show 2 Bars side by side by Zone.

Bar1 - Count of Open (From Work Lot Status Column ) Work Lots in the Last 30 Days (From Work Lot Opened Date (Actual) Column)

Bar2 - Count of Completed (From Work Lot Status Column) & Compliant (From Work Lot Compliance Column) in the Last 30 Days (From Work Lot Closed Date (Actual) Column)

Attached screenshots for your reference.

Any info with screenshots or guidelines would be of great help to me and others in a similar situation.

Hoping to hear soon.

Thanks a million for your time in advance.

Regards,

Chandu

010203050604

[Anonymous]

Hello Everyone,

I've uploaded the source data that I am working on to make it easier for you to understand my data and problem. This is exactly how I am importing data from SQL Database to Power BI.

https://drive.google.com/open?id=1CZQlN92OxVBXwdjkMmOaRyUSAz5nns0A5xhAuAb_4vY 

All I need to do is to show 2 Bars side by side by Zone.

Last 7 Days (will have to be To-Date from the beginning of the Project date - 7 Days):

Bar1 - Count of WorkLots Package Numbers that are "Open" (From Work Lot Status Column ) Work Lots in the Last 7 Days (From Work Lot Opened Date (Actual) Column)

Bar2 - Count of WorkLots Package Numbers that are "Completed" (From Work Lot Status Column) & "Compliant" (From Work Lot Compliance Column) in the Last 7 Days (From Work Lot Closed Date (Actual) Column)

Last 30 Days (will have to be To-Date from the beginning of the Project date - 30 Days):

Show 2 Bars side by side by Zone.

Bar1 - Count of WorkLots Package Numbers that are "Open" (From Work Lot Status Column ) Work Lots in the Last 30 Days (From Work Lot Opened Date (Actual) Column)

Bar2 - Count of WorkLots Package Numbers that are "Completed" (From Work Lot Status Column) & Compliant (From Work Lot Compliance Column) in the Last 30 Days (From Work Lot Closed Date (Actual) Column)

One of the challenges I found with this data is, data is not available in all the fields. I have joined both Work Lot Opened Date (Actual) and Work Lot Closed Date (Actual) to my CalendarDate.

I have also attached my data model as below:

I've got a feeling that I need to change the model especially around Work Lot Opened Date (Actual) and Work Lot Closed Date (Actual) or else would you reckon it should be possible just by using DAX?

Any help is greatly appreciated.

Thank you so much for reading and your thoughtful time.

Regards,

Chandu

[Anonymous]

Anyone... able to help with this..?

Thank you so much for your time in advance.

[amitchandak]

See, if this blog can help you :https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[Anonymous]

@amitchandakThanks for your eagerness to help me. However, I couldn't relate your link to my issue on hand! Can you please throw some more light. Thanks