The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi..
I have two data sets: Market Sales and Contact Details
Sample here:https://drive.google.com/open?id=1ZL5yvqDhUJ3eb1D0pr7FKmUNyE3qgvAN
Structure: Market Sales
Market Name | Time Period | Sales |
C1 | 201801 | 100 |
C1 | 201802 | 120 |
--- |
Contacts Data
Market | Time Period | Contact Type | No. of Contacts |
C1 | 201801 | T1 | 10 |
C1 | 201802 | T1 | 10 |
C1 | 201801 | T2 | 20 |
C1 | 201802 | T2 | 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!
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/
@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.
Country | Period | Sales | 6MMT (6 Months Prior but Excluding This Month) | YTD (From Last Year March to one month prior) |
C1 | 201711 | 80 | 0 | 0 |
C1 | 201712 | 90 | 80 | 80 |
C1 | 201801 | 100 | 170 | 90 |
C1 | 201802 | 110 | 270 | 190 |
C1 | 201803 | 120 | 380 | 300 |
C1 | 201804 | 130 | 500 | 420 |
C1 | 201805 | 140 | 630 | 550 |
C1 | 201806 | 150 | 690 | 690 |
C1 | 201807 | 160 | 750 | 840 |
C1 | 201808 | 170 | 810 | 1000 |
C1 | 201809 | 180 | 870 | 1170 |
C1 | 201810 | 190 | 930 | 1350 |
C1 | 201811 | 200 | 990 | 1540 |
C1 | 201812 | 210 | 1050 | 1740 |
@Anonymous
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
94 | |
88 | |
71 | |
65 |
User | Count |
---|---|
241 | |
124 | |
120 | |
81 | |
79 |