cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

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
Super User IV
Super User IV

@Prash - 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!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

Super User IV
Super User IV

@Prash , 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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

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

@Prash , 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)

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors