Move Values with blank date to Next quarter in DAX

[jaji]

Hi Team, I have a dataset with date column and Task column, In the task column we have "Not-Complete" and "Completed". The "Not-Complete" have blank dates and the "Completed" have dates filled. I want to generate a table that will display past and current quaters for "Completed" and have "Not Complete" in the next quarter, because it is assumed it will be completed then. I don't know how to even get started with that. Thanks for your help.

This is what my data looks like: 

And this is what I want:

Thanks!

[Anonymous · Accepted solution]

Hi @jaji ,

I create a table as you mentioned.

Then I think you can create a new table and here is the DAX code.

Table 2 = 
VAR CurrentQuarter =
    QUARTER ( TODAY () )
RETURN
    UNION (
        SELECTCOLUMNS (
            FILTER ( 'Table', 'Table'[Task] = "Completed" ),
            "ID", 'Table'[ID],
            "Date", 'Table'[Date],
            "Task", 'Table'[Task],
            "Quarter", FORMAT ( 'Table'[Date], "\QQ" )
        ),
        SELECTCOLUMNS (
            FILTER (
                ADDCOLUMNS (
                    'Table',
                    "NextQuarter",
                        IF ( QUARTER ( TODAY () ) = 4, 1, QUARTER ( TODAY () ) + 1 )
                ),
                'Table'[Task] = "Not-Complete"
            ),
            "ID", 'Table'[ID],
            "Date", DATE ( 2025, 1, 1 ),
            "Task", 'Table'[Task],
            "Quarter",
                FORMAT ( DATE ( 2025, ( CurrentQuarter * 3 ) + 1, 1 ), "\QQ" )
        )
    )

Finally you will get what you want.

Best Regards

Yilong Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jaji]

This is exactly what I was looking to do. Thanks!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information. Do not include anything that is unrelated to the issue or question.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...