cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RMV Member
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

LocationVersionStatusStart DateEnd DateGuarantee per month
Location A3Active1/1/20198/31/2019100
Location A2Not Active6/1/201812/31/201890
Location A1Not Active1/1/20185/31/201895
Location B2Active1/15/201912/31/201990
Location B1Not Active10/1/20181/14/2019100

 

2. Daily production Table

DateLocationProduction
1/3/2019Location A5
1/2/2019Location A1
1/1/2019Location A1
12/31/2018Location A2
12/30/2018Location A3
1/16/2019Location B3
1/15/2019Location B2
1/14/2019Location B3
1/13/2019Location B1

 

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

 

MonthLocationTotal Production (per month)Guarantee (per month)Delta
2019-01Location A1051005
2018-12Location A8890-2
2019-01Location B82100-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.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Daily and non daily data

Hi,

You may download my PBI file from here.

Hope this helps.

 

7 REPLIES 7
Highlighted
Community Support Team
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?

234.PNG

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

RMV Member
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) Smiley Happy

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,

 

Super User
Super User

Re: Daily and non daily data

Hi,

You may download my PBI file from here.

Hope this helps.

 

RMV Member
Member

Re: Daily and non daily data

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,

Super User
Super User

Re: Daily and non daily data

Not that i am aware of.

RMV Member
Member

Re: Daily and non daily data

@Ashish_Mathur , thanks for the help. 

Super User
Super User

Re: Daily and non daily data

You are welcome.

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 125 members 1,775 guests
Please welcome our newest community members: