Bar Chart: Prevent double counting from Detail table

[JustinDallas]

Hello Folks,

TL;DR The sum from a detail table will be double counted if it maps to multiple rows in the Fact table, and if the calendar key on the Fact table splits the chosen time property (Week, Month, WeekStarting).  How is this generally dealt with.

So I've got data similar to dummy script below

Widgets:
LOAD *,
DayStart(Floor(Num("WidgetCreationDate"))) AS '%fact_calendar_key'
;
LOAD *,
WidgetNumber AS '%widget_number',
Date#(WidgetCreationDateString, 'YYYY-MM-DD') AS 'WidgetCreationDate'
;
LOAD * Inline
[
    'WidgetNumber','WidgetCreationDateString'
    1, '2020-03-01'
    2, '2020-03-15'
]
;
DROP FIELD WidgetCreationDateString
;

WidgetInvoiceJoin:
LOAD * Inline
[
    %widget_number, %invoice_number
    1, 1
    2, 1
]
;

Invoice:
LOAD *,
InvoiceNumber AS '%invoice_number'
;
LOAD * Inline
[
    'InvoiceNumber', 'InvoiceTotal'
    1, '300'
]
;

MasterCalendar:
LOAD 
 DayStart(Floor(Num(TempDate))) AS %fact_calendar_key,
 Dual(Date(WeekStart(TempDate), 'YYYY-MM-DD'), Num(WeekStart(TempDate))) AS WeekStarting
;
LOAD 
 DATE(mindate + IterNo()) AS TempDate,
 mindate,
 maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate
;

//=== Get min/max dates from Field ===/
LOAD
 MIN(FieldValue('%fact_calendar_key', recno()))-1 as mindate,
 MAX(FieldValue('%fact_calendar_key', recno())) as maxdate
AUTOGENERATE FieldValueCount('%fact_calendar_key');

As you can see, I've got a single Invoice, and I have two widgets that were created in two different WeekStarting weeks.  Now, when I put the InvoiceTotal into a bar chart using the WidgetCreation date as the calendarkey in my Fact table, we see that the total appears in both weeks.

Of course, nothing is wrong with this data and it's logically consistent, but it confuses users and people who aren't familiar with what's going on.

Is there a way to somehow make the invoice total appear in the last widget creation date while maintaining the join?  Is there a better way to display this data from a UI perspective? 

Any help is greatly appreciated

[lorenzoconforti]

Not actually sure this is possible. You could get the last WeekStarting by InvoiceNumber within a measure

The problem is that when you use WeekStarting as a dimension, you will correctly get InvoiceTotal for all the widgest numbers associated to that invoice