How to show a unique count for a different period than the selected date range?

Kyle_Russell

Hi All,

I have a card where I am showing a year to date breakdown of assets, in order to show this I have 'This Year' selected for my date range. However ideally I want to show a count of unique accounts as at the end of the prior year, a count of unique accounts as at the end of the current month and the difference between these. I have tried doing this via the below beast mode:

CASE WHEN 'Reporting Date' = '31 December 2023' THEN COUNTDISTINCT(Client Entity ID) END.

However this seems to be pulling through blank values and I'm not sure where I'm going wrong, if anyone could help it would be much appreciated!

MarkSnodgrass

When you set the date range filter to This Year, you eliminate any previous year data. You would need to include the last 2 years in your date range filter and then use a beast mode to filter out the dates you don't want and add that to your filters.

This video may help you:

https://youtu.be/qxhV3gAZnk4

GrantSmith

Another alternative is to re-architect your data so that you have different offsets for your different dates. This way you can select the current year but show the current year, last year or any other period you've defined. I've done a write up on this in the past here:

https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons

DataMaven

In case you want to use your beast mode:

COUNTDISTINCT(CASE WHEN Reporting Date = '31 December 2023' THEN Client Entity ID END)

Obviously, that's not exactly it, given relative dates, etc, but I wanted to give you the order for that type of beast mode in case it helps you.

jrudd

Can you provide a sample of your data and a screenshot of the card you are trying to create?

MarkSnodgrass

When you set the date range filter to This Year, you eliminate any previous year data. You would need to include the last 2 years in your date range filter and then use a beast mode to filter out the dates you don't want and add that to your filters.

This video may help you:

https://youtu.be/qxhV3gAZnk4

GrantSmith

Another alternative is to re-architect your data so that you have different offsets for your different dates. This way you can select the current year but show the current year, last year or any other period you've defined. I've done a write up on this in the past here:

https://community-forums.domo.com/main/discussion/53481/a-more-flexible-way-to-do-period-over-period-comparisons

DataMaven

In case you want to use your beast mode:

COUNTDISTINCT(CASE WHEN Reporting Date = '31 December 2023' THEN Client Entity ID END)

Obviously, that's not exactly it, given relative dates, etc, but I wanted to give you the order for that type of beast mode in case it helps you.