Create a stacked bar chart visual by summing a column from two different table

Hi lbendlin ,

Apologise for the delay in response!

Thanks for your superb idea and I had worked on this and tested a lot.

We are very close to the solution, and here is my observation:

Dax code written was: 

Accrual kwh =
COALESCE ( [Profile], [Direct], [Target accrual] )

The dax measures inside COALESCE are as below:

Profile =
CALCULATE ( SUM ( Data[Units] ), FILTER ( Data, Data[Source] = "Profile" ) )

Target accrual =
CALCULATE (
SUM ( 'Missing Calendar date'[Target Unit] ),
USERELATIONSHIP ( 'Missing Calendar date'[Date], 'Calendar'[Date] )
)

Direct =
CALCULATE ( SUM ( Data[Units] ), FILTER ( Data, Data[Source] = "Direct" ) )

My sample dataset are as below:

Data table:

Missing Calendar date_1

Target Table

The above sample dataset can be found in below link:

COALESCE Function not working.pbix

When I used this measure 'Accrual kwh' in visual it includes only the first argument ' [Profile]' and doesn't include '[Direct]' & '[Target accrual]'. For example, INSE-965 has total of 2946.318(Accrual kwh) Units for Source Profile from 1st March 2023 to 8th March 2023 which shown as in below visual highlighed in red:

The same is shown in above tabular visual.

what we wanted to achieve is to fill(sum) the units for missing dates  in Data table with the Missing calendar date table. for we have 1st March 2023 to 8th March 2023 in Data table and from 09 march 2023 to 31st March 2023 is found in Missing Calendar date table.

The expected output should be as below:

That is 'Accrual kwh' measure will contain sum of values in order of

February -- >54 (Profile) + 50 (Direct) + 3.82 (Target Accrual ) =107.82

March ---> 2946.32 (Profile) + BLANK (Direct) + 20.58 (Target Accrual ) = 2966.9

I am confused in using COALESCE function.

Could you please help me to solve & whether we can use this or some other dax?

Thanks in advance.

 Ahmedx   lbendlin Ashish_Mathur  grantsamborn Greg_Deckler amitchandak onurbmiguel_ VijayP Idrissshatila