cancel
Showing results for
Did you mean:
Member

Daily and non daily data

Hi,

I need to make a comparison on 2 tables, one contains of daily data and another one containes non-daily data.

These are the tables:

1. Location Guarantee Table

 Location Version Status Start Date End Date Guarantee per month Location A 3 Active 1/1/2019 8/31/2019 100 Location A 2 Not Active 6/1/2018 12/31/2018 90 Location A 1 Not Active 1/1/2018 5/31/2018 95 Location B 2 Active 1/15/2019 12/31/2019 90 Location B 1 Not Active 10/1/2018 1/14/2019 100

2. Daily production Table

 Date Location Production 1/3/2019 Location A 5 1/2/2019 Location A 1 1/1/2019 Location A 1 12/31/2018 Location A 2 12/30/2018 Location A 3 1/16/2019 Location B 3 1/15/2019 Location B 2 1/14/2019 Location B 3 1/13/2019 Location B 1

What I'm looking for is the total of production in a month to be compared with the guarantee (per month).

As you may aware that the guarantee data in Table 1 can be differ within a month, just to make it simple, I'm good to use the guarantee number in the first date of the respective month.

The table below is the illustration of the calculation

 Month Location Total Production (per month) Guarantee (per month) Delta 2019-01 Location A 105 100 5 2018-12 Location A 88 90 -2 2019-01 Location B 82 100 -18

I have a separate table for Date and Location, which I expect to use in the graph.

Thus, I need to get how the total production of each month, the right guarantee for each month and difference between both.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

Re: Daily and non daily data

Hi,

Hope this helps.

7 REPLIES 7
Community Support Team

Re: Daily and non daily data

Hi RMV,

I am not clear about logic of calculation, so if possible, coud you please explain this to me more details?

How did you calculate Total Production, sum monthly based on daily table? Why only 2A and B location in Table, how did you choose this? I need to understand your logic, then I will help you more correctly.

Best Regards,
Zoe Zhi

Member

Re: Daily and non daily data

Hi Zoe,

Yes, that's right. It is a sum of production for each Location each month from the Daily Table.

Location A has total production = 105 in January 2019 (2019-01), and 88 in December 2018 (2018-12).

While Location B has total production = 82 in January 2019 (2019-01).

Daily Table only shows the illustration of the data structure, and not intended to show all data (to make it short)

So, if Location B apparently has production in December 2018 and/or moving forward, the result is expected to have the sum of production in each respective month for Location B. This is to be applied for Location A as well.

Regards,

Highlighted
Super User

Hi,

Hope this helps.

Member

Re: Daily and non daily data

If I understand correctly from your file, Table Location Guarantee is expanded to each day by adding a Custom Column in Query Editor. Just wondering, is there an alternative to use DAX formula for this same purpose?

Thanks,

Super User

Re: Daily and non daily data

Not that i am aware of.

Member

Re: Daily and non daily data

@Ashish_Mathur , thanks for the help.

Super User

Re: Daily and non daily data

You are welcome.

Announcements

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Get your latest community news and announcements.

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 47 members 892 guests
Recent signins: