contract end date in the next 90 days

[ACM_1000]

Hi all, 

I am trying to create a formula which will identify contract end dates which end 60 & 90 days from today's date. 

The current DAX syntax I am trying is 

= CALCULATE(SUM(Table1[Service Level End]),DATE(Table1[Todays Date],+60,DAY))

I created a column "today's date" which calculates the current day's date. The "service level end" is the column which has the end date of a customer's specific contract. 

Thank you

[ACM_1000]

Out of the list below, I am trying to create 3 separate KPI tiles that show how many customers have a service level end date in the next 30 days, 60days, and 90days. 

I put the simple formula in the last column =TODAY()

This would reference the counting point of +30, +60, +90. 

                                                                            Service Level Start, Service Level End, Todays Date

Customer ID	Customer Name
123	A	10/17/2014	10/16/2019	3/7/2019
334	B	12/4/2018	12/3/2021	3/7/2019
689	C	4/1/2017	3/31/2020	3/7/2019
2	D	1/1/2019	12/31/2023	3/7/2019
3	E	9/29/2017	9/28/2022	3/7/2019
5	F	1/1/2018	12/31/2021	3/7/2019
6	G	4/1/2018	3/31/2023	3/7/2019
7	H	3/1/2019	2/29/2024	3/7/2019
88	I	1/1/2018	11/30/2019	3/7/2019
3	J	6/28/2016	6/27/2021	3/7/2019
45	K	8/31/2017	8/30/2020	3/7/2019
76	L	1/1/2019	12/31/2021	3/7/2019
77	M	2/16/2018	2/15/2020	3/7/2019
78	N	5/1/2017	4/30/2022	3/7/2019
99	O	6/28/2018	9/2/2022	3/7/2019
100	P	4/28/2018	4/27/2023	3/7/2019
23	Q	2/16/2019	2/15/2020	3/7/2019
44	R	10/1/2018	12/31/2021	3/7/2019
567	S	6/29/2018	12/31/2021	3/7/2019
75	T	9/30/2016	9/29/2019	3/7/2019
556	U	12/21/2016	12/31/2021	3/7/2019
223	V	5/4/2018	5/3/2020	3/7/2019
445	W	10/1/2014	9/30/2019	3/7/2019
7765	X	12/13/2017	12/12/2020	3/7/2019
44576	Y	5/1/2017	4/30/2020	3/7/2019
55432	Z	10/22/2018	10/21/2023	3/7/2019

[v-yulgu-msft · Accepted solution]

Hi @ACM_1000 ,

Use three separate card visuals to display below three measures.

30 days =
CALCULATE (
    DISTINCTCOUNT ( Source_table[Customer Name] ),
    FILTER (
        ALLSELECTED ( Source_table ),
        Source_table[Service Level End] + 30
            = TODAY ()
    )
)

60 days =
CALCULATE (
    DISTINCTCOUNT ( Source_table[Customer Name] ),
    FILTER (
        ALLSELECTED ( Source_table ),
        Source_table[Service Level End] + 60
            = TODAY ()
    )
)

90 days =
CALCULATE (
    DISTINCTCOUNT ( Source_table[Customer Name] ),
    FILTER (
        ALLSELECTED ( Source_table ),
        Source_table[Service Level End] + 90
            = TODAY ()
    )
)

Best regards,

Yuliana Gu

[ACM_1000]

Hi Yuliana Gu, 

Thank you so much for the response. I am not getting any populated data in the card. 

[v-yulgu-msft]

Hi @ACM_1000 ,

You need a Card visual rather than Column chart visual.

Best regards,

Yuliana Gu

[ACM_1000]

Hi @v-yulgu-msft ,

I am using a card visual and getting the same results. 

[v-yulgu-msft]

Hi @ACM_1000 ,

New measures intead of calculated columns.

Best regards,

Yuliana Gu

[v-yulgu-msft]

Hi @ACM_1000 ,

Please illustrate your scenario with sample data and desired output, as mentioned: 

How to Get Your Question Answered Quickly

Regards,

Yuliana Gu