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.
|Snapshot date||Company||Overdue||Date Rank|
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
@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.
@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.
@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)
We are excited to announce the Power BI Super Users!
Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.