- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
Please kindly help.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
You may download my PBI file from here.
Hope this helps.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
You may download my PBI file from here.
Hope this helps.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Ashish_Mathur,
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Not that i am aware of.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
You are welcome.
Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
04-26-2024 05:53 AM | |||
08-02-2024 08:45 AM | |||
12-06-2023 06:06 AM | |||
03-20-2024 07:17 AM | |||
04-12-2024 08:43 AM |
User | Count |
---|---|
137 | |
107 | |
84 | |
59 | |
46 |