Discount Count on Max date with filter less than today()

[bideveloper555]

hi

below is the data and what am trying to achive.

i need to count max date less than today and exclude empty or null values.

ID	EndDate		ID	less than today		Max value		Count
1	01/01/2020		1	01/01/2020		01/01/2020		1
1	03/02/2019			03/02/2019
1
1	02/04/2022
1	03/04/2023
2	01/05/2018		2	01/05/2018		03/04/2020		1
2	05/06/2022			03/04/2020
2	03/04/2020
2
2
2	01/04/2023

[edhans]

@bideveloper555 can you provide more info? The below will give you a distinct count of the ID based on the EndDate column and today's date, but I'm not sure that is what you are looking for. Your data and question aren't 100% clear to me.

Max Less Than Today = 
VAR varCurrentDate = MAX(Data[EndDate])
VAR varMaxDate = 
CALCULATE(
    DISTINCTCOUNT(Data[ID]),
    FILTER(
        Data,
        Data[EndDate] < TODAY()
    ),
    REMOVEFILTERS(Data[EndDate])
)
RETURN
    varMaxDate

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly
How to provide sample data in the Power BI Forum

[bideveloper555]

hi

that was my bad...distinct is mispelled as discount.

am trying achive as below.

id	Date	As of Today
1	01/02/2020	inactive
1	01/08/2019	inactive
1	01/05/2021	Active

so i need to distinct count if they are expired and doesnt have any active . above scenario i need value as 0 because there still one active and which is greater than today.

id	Date	As of Today
2	01/02/2020	inactive
2	01/08/2019	inactive
2	01/05/2020	inactive

in this scenario,i need get discount of those dates as 1,as all the dates are less than today. and i need to count one based on recent date is less than today.

[v-zhenbw-msft]

Hi @bideveloper555 ,

How about the result after you follow the suggestions mentioned in my original post?

Could you please provide more details or expected result about it If it doesn't meet your requirement?

If you've fixed the issue on your own please kindly share your solution. If the above posts help, please kindly mark it as a solution to help others find it more quickly.

Best regards,

Community Support Team _ zhenbw

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

[bideveloper555]

hi

Sorry i didnt get back to you. Was off for few days due to sick.

this monring i tested it. it works perfectly.

Thank you for the help.

[v-zhenbw-msft · Accepted solution]

Hello @bideveloper555 ,

Let me confirm if my understanding is correct.

Do you want to display the date of Less Than Today and the Max date in two Table Visuals respectively?

If yes, we can create two calculation columns to meet your needs.

less than today = 
IF(
    'Table'[EndDate]<=TODAY(),'Table'[EndDate],BLANK())

Max value = 
var _x = CALCULATE(MAX('Table'[less than today]),FILTER('Table','Table'[ID]=EARLIER('Table'[ID])))
return
IF(
    _x='Table'[less than today],_x,BLANK()
)

Next, create two table visuals with these two columns. And set the column is not blank.

If you don't meet your requirements, could you show the exact expected result based on the table you shared?

Best regards

Community support team _ zhenbw

If this post helps,then consider Accepting it as the solution to help other members find it faster.

BTW, pbix as an attachment.

[bideveloper555]

hi

as per your test result, i need value in column Count.

which should be 1.thats all i need.

[amitchandak]

@bideveloper555 , not very clear. You can have a new column like

if([EndDate] =maxx(filter(table,[ID] =earlier([ID]) && [EndDate] <=today()),[EndDate]),[EndDate],blank())

if([EndDate] =maxx(filter(table,[ID] =earlier([ID]) && [EndDate] <=today()),[EndDate]),1,0)

[bideveloper555]

hi

that was my bad...distinct is mispelled as discount.

am trying achive as below.

id	Date	As of Today
1	01/02/2020	inactive
1	01/08/2019	inactive
1	01/05/2021	Active

so i need to distinct count if they are expired and doesnt have any active . above scenario i need value as 0 because there still one active and which is greater than today.

id	Date	As of Today
2	01/02/2020	inactive
2	01/08/2019	inactive
2	01/05/2020	inactive

in this scenario,i need get discount of those dates as 1,as all the dates are less than today. and i need to count one based on recent date is less than today.

[bideveloper555]

hi

am trying to get count of max date ,max date should be less than today from the list .

final result for above example is count = 1 for both ID(1,2)