Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Decrement/increment rank automatically after condition is met in DAX Power BI

I have a measure written in Power BI as below, where I have ranked the dates based on the recency where 1 will be the ranking of most recent date. When there are 2 dates this measure gives correct result but when there are 3 dates, it keeps the reference of date always as minimum and does the calculation.

Sample Data 

Snapshot date Company Overdue Date Rank
9-Sep-20A450611
9-Sep-20B211431
9-Sep-20C239191
7-Sep-20A218582
7-Sep-20B201432
7-Sep-20C454332
5-Sep-20A295953
5-Sep-20B456453
5-Sep-20C135463

 

Expected output 

Snapshot DateOverdueDifference 
5-Sep-2088,78688,786
7-Sep-2087,434-1,352
9-Sep-2090,1232,689
 

I have the above table at snapshot and company level. However, I want the final result as above table .

Is there a way to move the rank and keep increasing the min dates by 1 as the condition is met. So that I get Rank 1- Rank 2 , then rank 2- rank 3 and so on. Below DAX(measure) works for 2 snapshots but then it does nto work.

Difference = 
VAR MAX_DATE=min('Account and SA'[Date Rank])

VAR PREV_VAL=
CALCULATE(
    sum('Account and SA'[Overdue(Total)]),
    FILTER(
        ALLSELECTED('Account and SA'),
        'Account and SA'[Date Rank] > MAX_DATE
    )
)

return
sum('Account and SA'[Overdue(Total)])-PREV_VAL

 

 

5 REPLIES 5
Greg_Deckler
Super User
Super User

@Anonymous - Sorry, having trouble following, can you post sample data as text and expected output?


Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi,

 

I have highlighted the sample raw data and also expected output. I have tried the DAX but it is giving me partial results.

 

Pras

amitchandak
Super User
Super User

@Anonymous , Based on what I got that you need rank on date, see if this can help

calculate(count(Table[Snapshot Date]), filter(allselected(Table),Table[Snapshot Date]>=max(Table[Snapshot Date])))

 

Can you share sample data and sample output in a table format? Or a sample pbix after removing sensitive data.

Anonymous
Not applicable

I have highlighted the sample data and expected ouput. Also, the DAX I have shared is giving me partial results. 

@Anonymous , See if these formula and file attached after signature

//Measure 
Rank Measure = CALCULATE(DISTINCTCOUNT('Table'[Snapshot date]) ,FILTER(ALLSELECTED('Table'), [Snapshot date] >=max('Table'[Snapshot date])))

//Calc 
Calc Rank = RANKX(ALL('Table'),'Table'[Snapshot date],,DESC,Dense)

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.