Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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
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
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 :
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.
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
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.
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
Gift | Date 1 | Date 2 | Difference |
1 | 01/01/2002 | 25/02/2004 | 785 |
2 | 23/02/2019 | 12/03/2019 | 17 |
3 | 12/07/2015 | 15/09/2018 | 1161 |
4 | 03/03/2003 | 14/08/2006 | 1260 |
5 | 18/06/2000 | 01/02/2002 | 593 |
6 | 17/05/2017 | 25/06/2017 | 39 |
7 | 12/06/2019 | 05/07/2019 | 23 |
Thanks
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.
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
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:
The measures used to show me the dates of the gifts are as follows:
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
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
User | Count |
---|---|
122 | |
109 | |
94 | |
59 | |
57 |
User | Count |
---|---|
143 | |
119 | |
102 | |
71 | |
61 |