New table based on existing one with all dates from calendar

[KaZeK]

Dear Community,

I'd like to ask for support due to the fact I do not know how to find the solution. 

I have two tables:

1. Date 

2. List

Table Date contains full calendar. Table List contains Dates (only working days), NameID, Price of NameID and some Factor1 of NameID.

Important is that there are hundreds of NameID, Price and Factor is changing by days (values are not fixed and assign only to NameID - they are changing also for particular days). 

My problem is that I do not have holidays in Date collumn in Table List. 

I would like to create new table, lets call it Analysis Table, with collumn with all days (working days + holidays) and took all NameID, Price and Factor1 from table List. Of course all NameID values which will be assign to correct date with Price and Factor1 values. I'd like to have holydays with assigned NameID with empty Price and Factor1 collumns. 

How to do it?

Awaiting your feedback.

[tamerj1]

Hi @KaZeK 
Please try

Analysis =
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( 'Date'[Date'] ),
        SUMMARIZE ( 'List', 'List'[Name_ID], 'List'[Foctor1] )
    ),
    "Price", CALCULATE ( SUM ( 'List'[Price] ) )
)

[KaZeK]

After few modification I have similar warning:

"The expression refers to multiple columns. Columns cannot be converted to a scalar value."

[tamerj1]

@KaZeK 
Are you sure you are creating a calculated table?

[KaZeK]

To be honest I copy paste your formula and Calculate function could be used. But like I said I need to "copy" names ID and values from List table where one date is assigned to hundreds NameID and Price and Factor1 values.

It means I have the same quantity of NameID & Price & Factor1 for one particular date.

Eg.
for 2023.06.20, I have:

100x NameID rows

100x Price rows

100x Factor1 rows

Date           | Name ID   | Price       | Factor1

2023.01.01 | NameID_1 | Price_11 | Factor1_1   

...                |    ...            |    ...         | ...
2023.01.0n | NameID_n | Price_n   | Factor1_n

I'd like to create the same table but with dates (weekends & holidays) which are missing in List table.

[tamerj1]

@KaZeK 
Please provide screenshots with illustration.

[KaZeK]

Hello, thanks for support. It looks like there is some issue:

"This expression references a Relationship object named "f2cbd857-ad3f-4ea9-a12c-0e9bf782230f" which contains an error."

Where is the information in the formula that it should be linked by Date:
'List'[Date] <--> 'Date'[Date] ?

Maybe that's the problem?

[devanshi]

Analysis Table = CALCULATE(SUMMARIZE('List','List'[NameID],'List'[Price],'List'[Foctor],"abc",RELATEDTABLE('Date'[Date']))

[KaZeK]

Thanks for your response. But it doesn't work:

"The expression refers to multiple columns. Columns cannot be converted to a scalar value."

[devanshi]

Try this once ,

Analysis Table = CALCULATE(SUMMARIZE('List','List'[NameID],'List'[Price],'List'[Foctor],"abc",RELATED('Date'[Date']))

[KaZeK]

Hello, still the same issue

[devanshi]

Try this ,

Analysis Table = CALCULATETABLE(SUMMARIZE('List','List'[NameID],'List'[Price],'List'[Foctor]),RELATED('Date'[Date']))