Forum Discussion
DAX help on filter context
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 :
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
Community SupportHi , 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:
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
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
A 23/11/2021 10 A 20/12/2021 20 A 23/11/2022 30 B 15/10/2021 10 B 15/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
ENDby 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.
- v-yueyunzh-msft
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 :
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