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

[tdewitt274]

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?

[v-junyant-msft]

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

[tdewitt274]

Hello, Dino

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

Thanks,

Todd