Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

DAX help on filter context

Hi Team,

 

Please can you help me on below requirement.

 

I have Table A with below fields

Name    Date                    Amount

A          2022/11/23          10

A          2022/12/20          20

A          2022/11/23          30

 

1. From above table how to create two Date filters from one Date field like below

Pawwy_0-1669213700276.png

2. Once Dates are filtered like above how to calculate the below measure. In measure the dates should change dynamically based on user selections. 

 

Amount =

CASE WHEN (DATE1 >= '2022-11-21 00:00' AND DATE2  <'2022-11-22 00:00' ) THEN AMOUNT
ELSE 0
END 

 

Please help me to write the above dax

 

@v-janeyg-msft 

@amitchandak 

 

1 ACCEPTED SOLUTION

Hi , @Anonymous 

According to your description ,  you want to “CASE WHEN (DATE1 >= '2022-11-21 00:00' AND DATE2  < '2022-11-22 00:00' ) THEN AMOUNT”.

Here are the steps you can refer to:

(1)My test data is the same as yours.

(2)For your need , i recommend you to create a date table as slicer instead of using the date column in your table. You can click “New Table” and enter this:

 

Date = CALENDAR( FIRSTDATE('Table'[Date1]),LASTDATE('Table'[Date1]))

 

And we do not need to create relationship between two tables.

(3)We can click “New Measure”  to create a measure:

 

Measure = var  _min = MIN('Date'[Date])

var _max = MAX('Date'[Date])

var _t =FILTER('Table' , 'Table'[Date1]>= _min && 'Table'[Date1] <_max)

return

SUMX(_t,[Amount])

 

(4)Then we can put the ’Date‘[Date] on the sliver visual and the [Name] and the measure  on the visual and we will meet your need :

vyueyunzhmsft_0-1669283520613.png

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

 

View solution in original post

5 REPLIES 5
v-yueyunzh-msft
Community Support
Community Support

Hi , @Anonymous 

According to your description, you want to calcuate the sum of [Amount] per name and between the two date in the slicer visual.

If this , i think you do not need to create another date table, you just need to put the 'Table A' [Date]  in the visual and then you can get the data, like this:

vyueyunzhmsft_0-1669257192544.png

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

 

Anonymous
Not applicable

Hi @v-yueyunzh-msft Thanks for your reply. The above solution  is not working. please find the below expected output

 

Input Table:

Name  Date 1         Amount

A23/11/2021    10
A20/12/2021    20
A23/11/2022    30
B15/10/2021    10
B15/10/2022    70

 

output: for Date 1 = 23/11/2021 and Date 2 = 23/11/2022 selection

Name    Amount

A           30

 

Amount calculation: i am lookinf for dynamic dax expression for below calculation as user can select any of the dates

CASE WHEN (DATE1 >= '2022-11-21 00:00' AND DATE2  < '2022-11-22 00:00' ) THEN AMOUNT
ELSE 0
END 

 

by using date range i am getting amount for A=60 instead of 30 because its taking date 1 >= selected date and date2<=selected date. It should be date 1 >= selected date and date2< selected date.

Pawwy_0-1669281183380.png

 

 

Hi , @Anonymous 

According to your description ,  you want to “CASE WHEN (DATE1 >= '2022-11-21 00:00' AND DATE2  < '2022-11-22 00:00' ) THEN AMOUNT”.

Here are the steps you can refer to:

(1)My test data is the same as yours.

(2)For your need , i recommend you to create a date table as slicer instead of using the date column in your table. You can click “New Table” and enter this:

 

Date = CALENDAR( FIRSTDATE('Table'[Date1]),LASTDATE('Table'[Date1]))

 

And we do not need to create relationship between two tables.

(3)We can click “New Measure”  to create a measure:

 

Measure = var  _min = MIN('Date'[Date])

var _max = MAX('Date'[Date])

var _t =FILTER('Table' , 'Table'[Date1]>= _min && 'Table'[Date1] <_max)

return

SUMX(_t,[Amount])

 

(4)Then we can put the ’Date‘[Date] on the sliver visual and the [Name] and the measure  on the visual and we will meet your need :

vyueyunzhmsft_0-1669283520613.png

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

 

Best Regards,

Aniya Zhang

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

 

AilleryO
Memorable Member
Memorable Member

Hi,

 

First I think you should create 2 variables for your dates.

And to "grab" the value from your slicers, could you use MIN and MAX ? It should return what you expect otheriwse give us more details on your project.

Then compare :

VarDate1 >= DATE( 2022 , 11 , 21 ) && VarDate2 < DATE( 2022, 1, 22 )

For dates you should use date function, and for the AND you can use &&, or when you need OR ||.

 

Let us know first if it works with MIN and MAX before getting into the details of IF test, if needed 😉

 

Anonymous
Not applicable

Hi @AilleryO Thanks for your reply. Actually Date 1 and Date 2 fields are from two different tables. Table B is created from Table A like Table B = DISTINCT('Table A'[Date 1])

 

Pawwy_0-1669220486136.png

Sample data:

NameDate 1             Amount

A23/11/2021    10
A20/12/2021    20
A23/11/2022    30
B15/10/2021    10
B15/10/2022     70

 

 

Requirement is between Date 1 and Date 2 range what is the total amount by name.

 

I have created variables like below . But how to write return statement like 

if(Table A[Date1]>= Date1 &&Table B[Date2]< Date2,Amount,0)  -- It''s throwing error

Pawwy_1-1669220569484.png

 

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.