How to add a row to an existing table?

[Lindax12]

Hello,

I've got an excel export with the following data:

In this example: a working student is calculated with 0,5 FTE per month for every full month. But if the leaving date is not the last day of the month this month is not calculated at all.

My idea was to filter all leaving dates that are not the last day of month but I don’t know how to go on afterwards. In the example above I need to insert a new row for June. Is there a way to execute this step automatically? Thanks for your help in advance!!

[JosefPrakljacic]

Hello Lindax,

I joined your table with a DateTable by the [LeavingDate] Column that I took only the first row and was able to reproduce your expected result. Here is the example file

and here ist the DaxTable Code

NewCalcTable = 
UNION (
    'Ma-Table',
    TOPN (
        1,
        SELECTCOLUMNS (
            NATURALINNERJOIN ( 'Ma-Table', Kalendar ),
            "Month", [MonatNameLang],
            "Last Name", [Last Name],
            "First Name", [First Name],
            "Joining Date", [Joining Date],
            "Leaving Date", [Date]
        )
    )
)

If this post was helpful may I ask you to mark it as solution and give it some kudos?

Have a nice day!

BR,
Josef

[v-juanli-msft · Accepted solution]

Hi @Lindax12 

Enter the "June" data in excel or in Power BI (by entering data)

Then append queries

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-juanli-msft]

Hi @Lindax12 

I'm not clear about your requirement.

"But if the leaving date is not the last day of the month this month is not calculated at all"

This means:

If month is June, Leaving date is 2019/6/20, then this record should be considered as "not calculated", right?

"In the example above I need to insert a new row for June. "

Do you want to insert a row in the exsting Power BI data model?

"My idea was to filter all leaving dates that are not the last day of month but I don’t know how to go on afterwards"

You could give a sample and expected result.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Lindax12]

Hello Maggie,

right. I need the FTE's for every single month but in this case the row for June is missing.

Is it even possible to add a row in Power BI based on a calculation?

Thank you & best regards 

Linda