Forum Discussion

Zakaria_1980's avatar
Zakaria_1980
Post Patron
3 years ago
Solved

Need Help please

Hi,

I have such DB for which I need to see Tbl in Power BI, where he will keep me only 1 entry per project taking into account this control:

Check for each project is Start date is the same, should check the end date and keep for me the latest date and delete other rows for that project

if the start date is not the same, he will keep for me the latest date and delete other rows related to that project.

 

I appreciate if you could send me the PBI report so it will be easy for me to understand.

 

 

 

ProjectStart DateEnd date
A21/12/202118/01/2022
B16/10/202217/10/2022
C23/02/202222/04/2022
C23/02/202205/06/2022
C23/02/202225/08/2022
D21/12/201906/07/2022
K09/03/202002/02/2022
F22/06/202103/07/2022
Z15/11/202119/01/2022
Z15/04/202220/06/2022
W08/06/202102/02/2022
J08/04/202018/03/2022
X12/04/202220/04/2022
X29/06/202229/06/2022
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new table.

     

    New table = 
VAR _groupbymaxenddate =
    GROUPBY (
        Data,
        Data[Project],
        Data[Start Date],
        "@newenddate", MAXX ( CURRENTGROUP (), Data[End date] )
    )
VAR _maxstartdate =
    GROUPBY (
        _groupbymaxenddate,
        Data[Project],
        "@newstartdate", MAXX ( CURRENTGROUP (), Data[Start Date] )
    )
RETURN
    ADDCOLUMNS (
        _maxstartdate,
        "@enddate",
            MAXX (
                FILTER (
                    _groupbymaxenddate,
                    Data[Project] = EARLIER ( Data[Project] )
                        && Data[Start Date] = EARLIER ( [@newstartdate] )
                ),
                [@newenddate]
            )
    )

3 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    3 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new table.

     

    New table = 
VAR _groupbymaxenddate =
    GROUPBY (
        Data,
        Data[Project],
        Data[Start Date],
        "@newenddate", MAXX ( CURRENTGROUP (), Data[End date] )
    )
VAR _maxstartdate =
    GROUPBY (
        _groupbymaxenddate,
        Data[Project],
        "@newstartdate", MAXX ( CURRENTGROUP (), Data[Start Date] )
    )
RETURN
    ADDCOLUMNS (
        _maxstartdate,
        "@enddate",
            MAXX (
                FILTER (
                    _groupbymaxenddate,
                    Data[Project] = EARLIER ( Data[Project] )
                        && Data[Start Date] = EARLIER ( [@newstartdate] )
                ),
                [@newenddate]
            )
    )