How to Display Count of Value by Relative Date

[sheap069]

Hello,

I have data for applications, and I'd like to display the counts over the past 30, 90 and 365 days as shown below

	30 Days	90 days	365 days
Applications	10	100	120

I am not sure how to get the relative date (from today's date) and the counts in the columns. The date field I have in my data is by the Application's Received date. 

Thank you

[sheap069]

Hi @v-alq-msft 

Thank you very much. This helped and I got the result I wanted.

However, now I want to take it a step further. Say I have three different application types, and this will be in another column. How can I make a table like the following?

Application Type	30 Days	90 Days	365 Days	>1 year
Type 1	10	15	30	50
Type 2	5	20	25	40
Type 3	4	15	40	30

I also want to show the percentage of each application type in the table, so it would be like this:

Application Type	30 Days	90 Days	365 Days	>1 year
Type 1	10	15	30	50
Type 2	5	20	25	40
Type 3	4	15	40	30
All Apps	19	50	95	120
Percent Type 1	52%	30%	31%	42%
Percent Type 2	26%	40%	26%	33%
Percent Type 3	21%	30%	42%	25%

I also have another table in my data that shares the Application ID column, and there are also types of applications that I want to count, as shown above. However it doesn't have the received date column that I'm using here. How can I modify the formula to do the counts for the application types in that table as well?

Thank you very much

[v-alq-msft · Accepted solution]

Hi, @sheap069 

Based on your description, I created data to reproduce your scenario, The pbix file is attached in the end.

Table:

You may create measures as below.

30 Days = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        DATEDIFF([Date],TODAY(),DAY)<=30&&
        DATEDIFF([Date],TODAY(),DAY)>=0
    )
)

90 Days = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        DATEDIFF([Date],TODAY(),DAY)<=90&&
        DATEDIFF([Date],TODAY(),DAY)>=0
    )
)

365 Days = 
COUNTROWS(
    FILTER(
        ALL('Table'),
        DATEDIFF([Date],TODAY(),DAY)<=365&&
        DATEDIFF([Date],TODAY(),DAY)>=0
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@sheap069 for this kind of calculations, you should add a date dimension to your model, here is one of my blog posts talks about how to add one.

And once you have the date dimension in your model, use DATESINPERIOD and/or DATESBETWEEN to get the data.

Check my latest blog post Compare Budgeted Scenarios vs. Actuals I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[sheap069]

I've added the Date dimension to my data but I'm not sure how to use DATESINPERIOD to count my applications that were received in these times, then put the counts into a table. I want to put those categories as columns and have the counts as rows.