cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ashishphilip119 Helper II
Helper II

Trying something: Vlookup and Sumifs in Excel and want to do it in Power BI

Hi..

I have two data sets: Market Sales and Contact Details

Sample here:https://drive.google.com/open?id=1ZL5yvqDhUJ3eb1D0pr7FKmUNyE3qgvAN

 

Structure: Market Sales

Market NameTime PeriodSales
C1201801100
C1201802120
---  

 

Contacts Data

MarketTime PeriodContact TypeNo. of Contacts
C1201801T110
C1201802T110
C1201801T220
C1201802T2

20

 

Calculations is to be like: If for time period 201802: Contacts (T1+T2){20+20}/Sales in C1 country {201801+201712+201711+201710+201709+201708} (Sum of sales in 6 prior months)

 

I have tried to create columns for calculating the rolling 6 prior months sales in market table (but failed) and then bring it to complaints table and using a measure like max(6mmtsales)/sum(contacts) {max because on joining the two tables, the contacts table will have multiple records of the same sales value}

 

Please can someone help me with this. TIA!

4 REPLIES 4
Super User IV
Super User IV

Re: Trying something: Vlookup and Sumifs in Excel and want to do it in Power BI

My advice would convert time period to date in both tables and join with time dimesion and use the formula like the one in example below

New column in both tables

Date = date(left([time period],4),right([time period],2),1)

 

These are example formula, create like these

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH)) 
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-6,MONTH))  

 

Also, use a common Market dimension

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


ashishphilip119 Helper II
Helper II

Re: Trying something: Vlookup and Sumifs in Excel and want to do it in Power BI

@Greg_DecklerCan you advice an approach on this.

ashishphilip119 Helper II
Helper II

Re: Trying something: Vlookup and Sumifs in Excel and want to do it in Power BI

@amitchandakThis is the sample data and the process as well.

 

6MMT / YTD are the calculated columns I need. Emphasis on columns because data across two databases are at different levels and hence, measures won't work.

 

Logic is 6MMT is sum of sales for preceding 6 months or lesser in case data is not present.Ex: June 2019 is Dec 2018+Jan 2019+Feb 2019+Mar 2019+Apr 2019+May 2019

YTD is data from March of last fy to one month prior. Ex: May 2019 would be March 2019+April 2019.

Data is first 3 columns, I have added 2 additional columns with desired outputs.

 

CountryPeriodSales6MMT (6 Months Prior but Excluding This Month)YTD (From Last Year March to one month prior)
C12017118000
C1201712908080
C120180110017090
C1201802110270190
C1201803120380300
C1201804130500420
C1201805140630550
C1201806150690690
C1201807160750840
C12018081708101000
C12018091808701170
C12018101909301350
C12018112009901540
C120181221010501740
Super User IV
Super User IV

Re: Trying something: Vlookup and Sumifs in Excel and want to do it in Power BI

@ashishphilip119 

Please find the attached solution after the signature.

YTD you have to test on the bigger dataset as your description and expected output do not match

 





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
In case it does not help, please provide additional information and mark me with @
My Recent Blog -Week is not so Weak Proud to be a Super User! Connect on Linkedin


Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors