Instead of DAX formula, what is your expected output with the help of these two tables ?
Can you share the result of above query in table format(result expected in tableau)..it will be easy to understand
The below is the output is expected. It should be also aggregate (SUM) by year and month.
Calculation Logic: Day 1 is active only on product juice = 1/ (Expiry date- Manufacturing date +1) = 1/30 = 0.03
Day 2 is active on two products = 2 / ((Expiry date- Manufacturing date +1) on Product Juice + (Expiry date- Manufacturing date +1) on Product C) = 2/ 59 = 0.07
it remains same till day 21 later all three are active = 3/ ((Expiry date- Manufacturing date +1) on Product Juice +(Expiry date- Manufacturing date +1) on Product B)+ (Expiry date- Manufacturing date +1) on Product C) = 3/69 = 0.17
Needed to be aggregate (SUM) by year and month ie Year 2018 =3 and Month 5 =3 in this case
Days Earned Exposure 1 0.03 2 0.07 3 0.07 4 0.07 5 0.07 6 0.07 7 0.07 8 0.07 9 0.07 10 0.07 11 0.07 12 0.07 13 0.07 14 0.07 15 0.07 16 0.07 17 0.07 18 0.07 19 0.07 20 0.07 21 0.17 22 0.17 23 0.17 24 0.17 25 0.17 26 0.17 27 0.17 28 0.17 29 0.17 30 0.17
You need only these two columns in your output ie. Days, Earned Exposure.
What do you mean by this ?
"Needed to be aggregate (SUM) by year and month ie Year 2018 =3 and Month 5 =3 in this case"
I mean, the Exposure calculated in Days should also be aggregated by Year & Month.
For example If I use Year = SUM(all day exposure) on the year
If I use Month = SUM(all day exposure) on the month
So on the given dataset the Exposure portion on Year = 3 and Exposure portion on Month = 3
Hope it gives clear idea.
Create two Calculated fields:
DATEDIFF('day',[Manufacturing Date],[Expiry Date])+1
SUM(IF [Day]=1 THEN
IF [Product Name]='Juice' THEN 1/[Days Diff] END
ELSEIF [Day]>=2 AND [Day]<21 THEN
IF [Product Name]='Juice' or [Product Name]='Juice Set 10' THEN 1/[Days Diff] END
ELSE 1/[Days Diff]
Dax.twbx 21.6 KB
Hi Ombir rathee,
Thanks for the code. What if I have data for 3 years (2018 -2020) - all months too ? Because I see there was hard code value as passed in metric calculation (Earned Exposure).
Was there any optimized way of computing this logic?
Much appreciated your work. Please share me if there any better way of computing this measure. Thanks
What problem are you facing with above solution. After cross join, If one month contains 90 rows then 36 months will contain 3240 rows which isn't a huge dataset.
If you see the granularity in the time dimension is on Day not at Month level.
According to your assumption, If one month contains 90 rows then the resulted data volume would be 90*365*3 then expected 100,000 rows.
The real dataset I have, had more than 40K transactions per month then the expected growth would be 43M which is very huge thou.
Hope it gives clear background! Kindly let me know if you have any work-around solution to avoid growing data size.
If you've data in two tables like you shared then cross join is required to get the desired output which ultimately increase the rows count. I can't think of any other way. The DAX you shared also doesn't contains the logic of Day1, Day2 and Day21. I think it is implemented in another Dax. You should found the cross join logic in DAX also.
Because I see there was hard code value as passed in metric calculation (Earned Exposure).
I don't understand this. Which value is hardcoded ?
Could you please post another dataset which looks similar to your real data for multiple months/years so that I can test for performance.
Hi Ombir Rathee,
Ideally I didn't use any other DAX calculations to devise logic separately Day 1, Day 2 and Day 21. Attached is the excel sample report and you can see the DAX calculations in Power pivot (Ribbon) :
SampleData.xlsx 203.9 KB