Distribute cost and add rows?

[ays1977]

Hi,

New to PowerBI and DAX and hope you can help me with the solution. I have cost my income statment the I want to allocate/distribut to a internal number. For some cost I already have the cost on the correct internal number but for som OH cost I need to distribute the cost. For each month the share of each internal number will varies and I also have 2 different result units. 

The figures from the income statment as below:

Date	Ver.Nr.	Account	Result unit	Internal number	Amount
20230102	34113252	Rent	1		15000
20230102	34113252	Income	1	12345	300
20230103	34126272	Insurance	1		25000
20230103	34126272	IT	1		3000
20230103	34126272	Income	2	23456	500
20230103	34126272	Insurance	2		20000
20230103	34126272	Rent	2		10000
20230201	34126255	Rent	1		50000

I also have a table with the share for each internal number per month and resultunit.

Period	Result unit	Internal number	Share
jan-23	1	12345	25%
jan-23	1	456789	25%
jan-23	1	321654	50%
jan-23	2	23456	40%
jan-23	2	987654	35%
jan-23	2	987999	25%
feb-23	1	12345	35%
feb-23	1	456789	12%
feb-23	1	321654	53%

What I need, if it´s possible, is for each row that don´t have an internal number I need to slit the amout according the the share for that month and add rows with the date, account, result unit and internal number so I end up with a table as below. Is this possible to do?

Date	Ver.Nr.	Account	Result unit	Internal number	Amount
20230102	34113252	Rent	1	12345	3750	0,25 of 15000
20230102	34113252	Rent	1	456789	3750	0,25 of 15000
20230102	34113252	Rent	1	321654	7500	0,5 of 15000
20230102	34113252	Income	1	12345	300
20230103	34126272	Insurance	1	12345	6250	0,25 of 25000
20230103	34126272	Insurance	1	456789	6250	0,25 of 25000
20230103	34126272	Insurance	1	321654	12500	0,5 of 25000
20230103	34126272	IT	1	12345	750	0,25 of 3000
20230103	34126272	IT	1	456789	750	0,25 of 3000
20230103	34126272	IT	1	321654	1500	0,5 of 3000
20230103	34126272	Income	2	23456	500
20230103	34126272	Insurance	2	23456	8000	0,4 of 20000
20230103	34126272	Insurance	2	987654	7000	0,35 of 20000
20230103	34126272	Insurance	2	987999	5000	0,25 of 20000
20230103	34126272	Rent	2	23456	4000	0,4 of 10000
20230103	34126272	Rent	2	987654	3500	0,35 of 10000
20230103	34126272	Rent	2	987999	2500	0,25 of 10000
20230201	34126255	Rent	1	12345	17500	0,35 of 50000
20230201	34126255	Rent	1	456789	6000	0,12 of 50000
20230201	34126255	Rent	1	321654	26500	0,53 of 50000

Regards

[wdx223_Daniel · Accepted solution]

NewTable=
FILTER (
    SELECTCOLUMNS (
        GENERATE (
            IncomeStatement,
            SELECTCOLUMNS (
                FILTER (
                    ShareTable,
                    ShareTable[Period] = FORMAT ( IncomeStatement[Date], "mmm-yy" )
                        && ShareTable[Result unit] = IncomeStatement[Result unit]
                ),
                "Res_U", ShareTable[Result unit],
                "Inter_Num", ShareTable[Internal number],
                "share", ShareTable[Share]
            )
        ),
        "Date", IncomeStatement[Date],
        "Ver.Nr.", IncomeStatement[Ver.Nr.],
        "Account", IncomeStatement[Account],
        "Result Unit", IncomeStatement[Result unit],
        "Internal Number", COALESCE ( IncomeStatement[Internal number], [Inter_Num] ),
        "Amount",
            IF (
                ISBLANK ( IncomeStatement[Internal number] ),
                IncomeStatement[Amount] * [share],
                IF ( IncomeStatement[Internal number] = [Inter_Num], IncomeStatement[Amount] )
            )
    ),
    [Amount]
)

[ays1977]

Thank you wdx223_Daniel but when I try i get an error message that says that "DAX Comparisons cannot compare values of type Date with values of type TEXT". Can you please share excelfiles and pbix-file or do you have the solution how I can change this? Regards

[wdx223_Daniel]

you must keep the data types be consistent. Date value can not compare with Text value.

here is the file for your reference.