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
UK_User123456
Resolver I
Resolver I

Ranking and calculating the difference

Hi All,

 

I am working with Ranking and currently using RankX. I have multiple gift dates that could have been given by the same person over a period of time. I have ranked each of the gifts by date and used 2 measures to give me the last gift and the gift prior to this. Is there anyway of being able to calculate the difference between the last gift and gift prior to last?

 

Im assuming because I have used a measure to give me the gift dates I require that I cannot use another measure to do

last gift - gift prior to last? When I do this, it always gives me a date and not a number any ideas? I cannot change the format or data type as these options are greyed out.

 

TIA

2 ACCEPTED SOLUTIONS
MartynRamsden
Solution Sage
Solution Sage

Hi @UK_User123456 

 

Have you tried using the VALUE function to covert the result into a number?

For example:

 

Date Diff = VALUE ( [Last gift] - [Gift prior to last] )

 

Best regards,

Martyn

View solution in original post

Hi @UK_User123456 

 

How about this?

 

Date Diff = 
IF ( 
    NOT ISBLANK ( [Gift date prior to last] ),
    VALUE ( [Last gift date] - [Gift date prior to last] ),
    BLANK()
)

 

Best regards,

Martyn

View solution in original post

19 REPLIES 19
MartynRamsden
Solution Sage
Solution Sage

Hi @UK_User123456 

 

Have you tried using the VALUE function to covert the result into a number?

For example:

 

Date Diff = VALUE ( [Last gift] - [Gift prior to last] )

 

Best regards,

Martyn

@MartynRamsden  is there a way of incorporating a blank statement in with your response, so that when there is a blank, it returns nothing?

 

TIA

Hi @UK_User123456 

 

There will definitely be a way to handle the blank values but it depends where they occur.

Do you have an example?

 

Best regards,

Martyn

@MartynRamsden if a gift was given as of yesterday but there was no previous gift at all it would show the value of -43658 rather than just show blank

 

Last Gift Date            Gift Date prior to last               Date Diff

03/10/2019                 09/09/2019                             30 days

28/11/2019                                                                 -43658

@MartynRamsden do you know why when putting the date diff into a visualisation (line and stacked column chart) it only shows 1?

 

I have added in 3 categories so anything under 730 days is 0-24m, >731 but <1460 is 25-48m and then anything else is older, but it doesnt do it in a visual? the formula I used is :

 

Category Grouping 2 =

IF([Date Diff between latest gift & gift prior to last gift] <=730,"0 - 24",

IF([Date Diff between latest gift & gift prior to last gift] >= 731 && [Date Diff between latest gift & gift prior to last gift] <=1460, "25 - 48",

IF([Date Diff between latest gift & gift prior to last gift] >=1461,"Older",""

)))
 
Any ideas?

Hi @UK_User123456 

 

I think I know what your asking for but it would be much easier to see.

Any chance you could share your pbix? Be sure to remove any sensitive date before you do.

 

Best regards,

Martyn

It wouldnt allow me to upload my pbix file but my report has sensitive data and I cannot replicate it. I have uploaded a screenshot of the issue.

 

Capture.PNG

Hi @UK_User123456 

 

Sorry, I'm struggling to see what you're trying to achieve here.

Are you wanting to show the number of donations per category, the total number of 'diff days' per category or something completely different?

 

Seeing your report would really help but if you're not able to share your pbix (via OneDrive or Dropbox, with link shared here), could you paste some sample data instead?

 

Any additional info you can provide would really help.

 

Best regards,

Martyn

@MartynRamsden 

 

I wanted to show the number of donations per category. not sure why my graph only shows the value of 1 with the category of 0-24?

 

I have another question but feel like I am confusing the situation at the moment.

 

 

Hi @UK_User123456 

 

If you want to count the number of donations, you'll need to add the categorisation as a calculated column, not a measure.

You'll also need a measure to calculate the number of donations.

 

In your chart, you can then put categorisation on the axis and your 'Number of donations' measure in the values and that should give you what you're looking for.


If you can provide a small sample of data, I can help you with the relevant DAX expressions.

 

Best regards,

Martyn

 

@MartynRamsden many thanks for your response, please find below the snippet of sample data

 

GiftDate 1Date 2Difference
101/01/200225/02/2004785
223/02/201912/03/201917
312/07/201515/09/20181161
403/03/200314/08/20061260
518/06/200001/02/2002593
617/05/201725/06/201739
712/06/201905/07/201923

 

Thanks

Hi @UK_User123456 

 

You can use the following expression to categorise each gift. Remember, this needs to be added as a column!

Category = 
SWITCH (
    TRUE (),
    'Table'[Difference] <= 730, "0 - 24",
    'Table'[Difference] >= 730 && 'Table'[Difference] <= 1460, "25 - 48",
    'Table'[Difference] >= 1461, "Older"
)

 

Then create a measure to count the number of donations. Something simple like:

Number of donations = COUNTROWS ( 'Table' )

or

Number of donations = DISTINCTCOUNT ( 'Table'[Gift] )

 

You can then create a new visualisation with the 'Category' column on the axis and the 'Number of donations' measure in the values.

That should give you the result below.

 

Capture1.JPG   Capture.JPG

 

Hope that helps?

 

Best regards,
Martyn

 

 

 

 

 

 

@MartynRamsden this does do the job im after, however, with the categories, it only shows me one category which is 0-24m. I think this is in part to the calculation that you posted at the beginning which shows the difference between last gift and gift prior to it which I used the following calculation

 

Date Diff between latest gift & gift prior to last gift =
IF(
NOT ISBLANK( [Gift Prior To Last Gift] ),
VALUE([Last Gift] - [Gift Prior To Last Gift]),
BLANK()
)
 
The above calculation is correct and works but when I call this calculation in the switch statement, it doesnt categorise the results.
 
Active Donors Category =

SWITCH(

TRUE(),
[Date Diff between latest gift & gift prior to last gift] <=730, "0 - 24m",
[Date Diff between latest gift & gift prior to last gift] >= 731 && [Date Diff between latest gift & gift prior to last gift] <= 1460, "25 - 48m",
[Date Diff between latest gift & gift prior to last gift] >= 1461, "Older"

)
 
Any ideas?

@UK_User123456 

 

I think it may have something to with the definition of your original measures ( 'Last Gift' and 'Gift Prior To Last Gift') being calculated in the wrong context. However, without seeing your report in its entirety, it's impossible to say.

 

You could try this as the definition for the column - it doesn't reference any measures so should be computed in the correct row context:

Active Donors Category = 
VAR DateDiff = 
IF(
NOT ISBLANK( 'Table'[Date 1] ),
VALUE( 'Table'[Date 2] - 'Table'[Date 1] ),
BLANK()
)
VAR Result =
SWITCH (
    TRUE (),
    DateDiff <= 730, "0 - 24",
    DateDiff >= 730 && DateDiff <= 1460, "25 - 48",
    DateDiff >= 1461, "Older"
    )
RETURN Result

 

You'll need to replace the table and column references with those in your actual report.

 

Best regards,

Martyn

@MartynRamsden ok so I am still getting the same answer of one category.

 

Could it be the way the ranking is working? currently the calculation I have for ranking is:

 

Gift Date Rank (DESC) =
RANKX(
 
FILTER(
'All Gifts lookup',
'All Gifts lookup'[ID] = EARLIER('All Gifts lookup'[ID]) &&
'All Gifts lookup'[FY] = EARLIER('All Gifts lookup'[FY] )
),

'All Gifts lookup'[Gift Date],
,
DESC, 
Dense 
)

 

The measures used to show me the dates of the gifts are as follows:

 

Gift Prior To Last Gift =
CALCULATE(
MAX('All Gifts lookup'[Gift Date] ),
'All Gifts lookup'[Gift Date Rank (DESC)] = 2
)
 
and 
 
Last Gift =
CALCULATE(
MAX('All Gifts lookup'[Gift Date] ),
'All Gifts lookup'[Gift Date Rank (DESC)] = 1
)

Hi @UK_User123456 

 

I'm sure that the solution here is a simple one but it's almost impossible to troubleshoot without seeing how everything fits together in your model.

I appreciate you can't share your pbix 'as is' due to to sensitive data but are you able to anonymise it or create an exact replica of your model (including all measure and calculated columns) with a small sample of dummy data?


Best regards,

Martyn

 

 

@MartynRamsden I will try and get a file uploaded with dummy data.

 

Thanks for your help.

Hi @UK_User123456 

 

How about this?

 

Date Diff = 
IF ( 
    NOT ISBLANK ( [Gift date prior to last] ),
    VALUE ( [Last gift date] - [Gift date prior to last] ),
    BLANK()
)

 

Best regards,

Martyn

@MartynRamsden absolutely perfect, works like a dream. Many thanks

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.