DAX Rownumber() to act like SQL Row_Number() with Dynamic Date

tdewitt274 — Mon, 27 Nov 2023 18:57:00 GMT

Hello, everyone

First off, I'd like to admit that I'm not well versed in DAX. So, there might be a simpler process.

I have a request to simulate the below SQL Server query using a dynamic date in Power BI. Essentially, the most recent time that a "UniqueID" is used on or before a specific date.

WITH cte_test AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY UniqueID ORDER BY workDate desc) AS ROWID
FROM table1
WHERE workDate <= '2023-11-01'
)
SELECT max(workDate) AS workDate, status, count(UniqueID) AS countUniqueID, sum(AMOUNT_DUE) AS AMOUNT_DUE, sum(PRINCIPAL_AMOUNT) AS SUM_PRINCIPAL_AMOUNT, sum(INTEREST_AMOUNT) AS sum_INTEREST_AMOUNT
FROM cte_test
WHERE ROWID = 1
GROUP BY status;

I was able to get the data to come in as expected using the following DAX and a multiple select box. The user wants the ability to change the date with the select box and update the data to use the new date as the "origin".

rwDateRank = ROWNUMBER(
    ALLSELECTED(impData[DWORK_OF_DATE],impData[LOAN_ID])
    ,ORDERBY(
        'impData'[DWORK_OF_DATE],DESC
    )
    , DEFAULT
    , PARTITIONBY('impData'[LOAN_ID])
)

The issue I am running into is that I cannot aggregate the values by Status. If I remove the Unique ID or Work Date, the data disappears and only shows the headings.

I need to aggregate the data to show something like the below.

Anyone have any suggestions?

Re: DAX Rownumber() to act like SQL Row_Number() with Dynamic Date

Anonymous — Wed, 29 Nov 2023 02:24:48 GMT

Hi tdewitt274 ,

Please provide some sample date and can you explain how these figures were calculated?

I need to know the formula to find the DAX for you.

Best Regards,
Dino Tao

Re: DAX Rownumber() to act like SQL Row_Number() with Dynamic Date

tdewitt274 — Thu, 30 Nov 2023 14:13:47 GMT

Hello, Dino

These are just COUNT and SUM the "columns" section of the "Build visual" section.

Thanks,

Todd

Re: DAX Rownumber() to act like SQL Row_Number() with Dynamic Date

hstance — Fri, 28 Mar 2025 19:29:57 GMT

ROWNUMBER IN DAX IS GARBAGE.

topic Re: DAX Rownumber() to act like SQL Row_Number() with Dynamic Date in DAX Commands and Tips

DAX Rownumber() to act like SQL Row_Number() with Dynamic Date

Re: DAX Rownumber() to act like SQL Row_Number() with Dynamic Date

Re: DAX Rownumber() to act like SQL Row_Number() with Dynamic Date

Re: DAX Rownumber() to act like SQL Row_Number() with Dynamic Date