Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I've been trying to create DAX functions to calculate program overlapping days, but so far still unsuccessful.
What my goal is to calculate the number of days two or more program overlapped (program dates) and the dollar amount associate with the overlap.
I have a very large data table of following information:
Line # | Marketing Partner Code | Marketing Item Code | Marketing Program Code | Program Start Date | Programe End Date | Expense |
1 | Company A | PA | Hola 1 | Jan 1 2019 | Jan 31 2019 | $500,000 |
2 | Company A | PA | Hola 2 | Jan 15 2019 | Jan 18 2019 | $120,000 |
3 | Company A | PA | Hola 3 | Jan 30 2019 | Feb 20 2019 | $95,000 |
4 | Company B | PB | Hola 1 | Jan 1 2019 | Jan 31 2019 | $500,000 |
I also created a calendar table so that the user can define the program validity dates like so:
In my business what can happen is for a given marketing partner and marketing item, there can be multiple marketing campaigns that might run simultaneous (either full or partial duration) and I want to know given those overlapping days, how much additional expense we spent/day.
I consider overlapping days with the following logic:
With those overlapping days (line 1 to 3 overlaps: 4 days + 3 days), I want to get the simple average expense/day (e.g., Company A + PB + Program 1 Marketing Expense divided by the total validity days of Program 1 of the same combination). So
With this information, I am going to multiply the average expense/day, days overlapping AND product volume data (which is another table)
Would appreciate any help or tips. Thanks in advance!
Solved! Go to Solution.
Hi,
Please try this measure:
Measure =
SUM ( 'Table'[Expense] )
/ COUNTROWS (
FILTER (
CALENDAR (
MIN ( 'Table'[Program Start Date] ),
MAX ( 'Table'[Programe End Date] )
),
COUNTROWS (
FILTER (
'Table',
'Table'[Program Start Date] <= [Date]
&& 'Table'[Programe End Date] >= [Date]
)
) >= 2
)
)
The result shows:
Best Regards,
Giotto Zhi
Hi,
Please try this measure:
Measure =
SUM ( 'Table'[Expense] )
/ COUNTROWS (
FILTER (
CALENDAR (
MIN ( 'Table'[Program Start Date] ),
MAX ( 'Table'[Programe End Date] )
),
COUNTROWS (
FILTER (
'Table',
'Table'[Program Start Date] <= [Date]
&& 'Table'[Programe End Date] >= [Date]
)
) >= 2
)
)
The result shows:
Best Regards,
Giotto Zhi
@Greg_DecklerThank you for the suggestion, but the code doesn't seem to work as intended, but this was a new DAX function that I think would be useful for my future use!
@v-gizhi-msftThank you so much for the suggestion. I understand the logic behind the division, but don't fully understand the first "filter" and the reason behind putting the 2nd "countrow" Could you help me understand please?
I have a very large data (50k+ lines) that has numerous Marketing Partner Code, Program Code, etc... Is there a way to adjust the code so that I can get the data based on the combination (concanate) of Partner Code and Marketing Item (e.g., if Partner C has a certain item with program and so does Partner G within certain length of overlapping program dates)
Also, my "date" slicer doesn't seem to interact with the data. How do I link my separate calendar table that I created?
Hi,
The first filter is to generate a duration date calendar, the second filter is to count the rows that the date from calendar table which is between start date and end date from another main table.
Best Regards,
Giotto Zhi
I would think it would be something along the lines of:
Overlap Column =
VAR __Line = [Line]
RETURN
COUNTROWS(
INTERSECT(
GENERATE([Program Start Date],[Program End Date]),
GENERATE(
MINX(FILTER(ALL('Table'),[Line] <> __Line),[Program Start Date]),
MAXX(FILTER(ALL('Table'),[Line] <> __Line),[Program End Date])
)
)
)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
97 | |
82 | |
74 | |
66 |
User | Count |
---|---|
121 | |
105 | |
102 | |
82 | |
72 |