cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bideveloper555
Helper IV
Helper IV

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

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.

IDEndDate IDless than today Max value Count
101/01/2020 101/01/2020 01/01/2020 1
103/02/2019  03/02/2019    
1        
102/04/2022       
103/04/2023       
201/05/2018 201/05/2018 03/04/2020 1
205/06/2022  03/04/2020    
203/04/2020       
2        
2        
201/04/2023       
1 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()
)

dis1.jpg

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

dis2.jpg

dis3.jpg

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.

View solution in original post

9 REPLIES 9
edhans
Super User
Super User

@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.

edhans_0-1598976865332.png

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

hi

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

 

am trying achive as below.

idDateAs of Today
101/02/2020inactive
101/08/2019inactive
101/05/2021Active

 

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.

idDateAs of Today
201/02/2020inactive
201/08/2019inactive
201/05/2020inactive

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.

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.

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.

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()
)

dis1.jpg

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

dis2.jpg

dis3.jpg

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.

hi

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

which should be 1.thats all i need.

amitchandak
Super User
Super User

@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)

hi

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

 

am trying achive as below.

idDateAs of Today
101/02/2020inactive
101/08/2019inactive
101/05/2021Active

 

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.

idDateAs of Today
201/02/2020inactive
201/08/2019inactive
201/05/2020inactive

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.

 

 

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)

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.