Power BI how to add a new row based on condition after group

user_19449368 0
Viewed 184

I have a table like this:

enter image description here

I want to create a default value for each group(each month should have a default amount 0), I know we should do group function first, but I do not know want to do next, very appreciate who give me help

powerbi
1 Answers

I'm not sure exactly where you are going with your request. I understand it as: Adding a row for the first day of each month with a default value of zero.

This is not easy DAX. It's a bit complicated, so feel free to provide more info about your need, there may be another way more simple way.

The first step is to generate a table of the first day of each month. After a quick search, I've found a way in another answer and I was able to modify it a bit.

TABLE2 =
SELECTCOLUMNS(
    FILTER(
        ADDCOLUMNS(
            CALENDAR(
                DATE(2022, 01, 01),
                DATE(2022, 12, 01)
            ),
            "IsFDOM",
            IF(
                DAY([Date]) = 1,
                TRUE(),
                FALSE()
            )
        ),
        [IsFDOM] = TRUE()
    ),
    "DATA",
    [Date],
    "AMOUNT",
    0
)

What it does:

  1. It generate a calendar between two dates I've arbitrary set : January 1st to Decembre 1st.
  2. It adds a columns in this generated table IsFDOM (Is First Day Of Month) that return True is it is.
  3. This table is then filtered to keep only the rows where IsFDOM is True.
  4. It is wrapped in a SELECTCOLUMNS to keep and rename the columns you want and to match your existing table.

Next, you want to merge your existing table with this newly generated table. The DAX function to do it is UNION and requires both table to have the same format - i.e same number of columns.

TABLENEW = 
UNION(
    TABLE1,
    FILTER(
        TABLE2,
        NOT TABLE2[DATA] IN VALUES(TABLE1[DATA])
    )
)

I've named you existing table TABLE1 in this example. The FILTER is used to not merge rows (dates) that already exist in TABLE1.

And, to make things even nicer. We can bypass the creation of TABLE2 by embedding it in the table expression of TABLENEW :

TABLENEW = 
VAR MaxDate = 
MAX(TABLE1[DATA])
VAR TMP_Calendar =
FILTER(
    SELECTCOLUMNS(
        SUMMARIZE(
            FILTER(
                ADDCOLUMNS(
                    CALENDAR(
                        DATE(2022, 01, 01),
                        DATE(2022, 12, 01)
                    ),
                    "IsFDOM",
                    IF(
                        DAY([Date]) = 1,
                        TRUE(),
                        FALSE()
                    )
                ),
                [IsFDOM] = TRUE()
            ),
            [Date],
            "default",
            0
        ),
        "DATA",
        [Date],
        "AMOUNT",
        [default]
    ),
    NOT [DATA] IN VALUES(TABLE1[DATA]) &&
    [DATA] <= MaxDate
)
RETURN
UNION(
    TABLE1,
    TMP_Calendar
)

The calendar formerly known as TABLE2 is now created in a variable TMP_Calendar. To make thing nicer, MaxDate calculate the max date of your existing table and only merge for existing date.

user_58872670
Related