Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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-20 | A | 45061 | 1 |
9-Sep-20 | B | 21143 | 1 |
9-Sep-20 | C | 23919 | 1 |
7-Sep-20 | A | 21858 | 2 |
7-Sep-20 | B | 20143 | 2 |
7-Sep-20 | C | 45433 | 2 |
5-Sep-20 | A | 29595 | 3 |
5-Sep-20 | B | 45645 | 3 |
5-Sep-20 | C | 13546 | 3 |
Expected output
Snapshot Date | Overdue | Difference |
5-Sep-20 | 88,786 | 88,786 |
7-Sep-20 | 87,434 | -1,352 |
9-Sep-20 | 90,123 | 2,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
@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.
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
@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.
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)
User | Count |
---|---|
141 | |
113 | |
104 | |
78 | |
64 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |