topic Re: Calculating values based on Date selection from slicer in DAX Commands and Tips

Calculating values based on Date selection from slicer

Happy2023_05 — Fri, 11 Nov 2022 10:33:25 GMT

hi,

I want to calculate values based on date slicer selection.

for eg date slicer 01/02/2022 to 01/05/2022. I need to write DAX. can someone help on this?

for eg calculate(sum(x), date in slicer selection). what DAX function can be used to get this?

Re: Calculating values based on Date selection from slicer

serpiva64 — Fri, 11 Nov 2022 11:26:16 GMT

Hi,

i think you don't need to write any dax.

You only need to create a relationship between your calendar table and your fact table.

Re: Calculating values based on Date selection from slicer

v-xiaotang — Mon, 14 Nov 2022 08:33:46 GMT

Hi Happy2023_05

Thanks for reaching out to us.

If you create a relationship between the calendar table and the fact table, then when you filter the date in the slicer, the data in the fact table in visual will also be filtered. If you don't want the number of data rows in visual to be affected, please try the following method:

Sample (see sample file attached below)

(1) create a calendar table

calendar = calendar(DATE(2021,1,1),DATE(2022,12,31))

(2) create the measure below

Measure1 = var _start=MINX(ALLSELECTED('calendar'),[Date]) var _end=MAXX(ALLSELECTED('calendar'),[Date]) var _sum= SUMX(FILTER('Table','Table'[Date]>=_start && 'Table'[Date]<=_end),[value]) return "date range:"&_start&"-"&_end&": "&_sum

Best Regards,

Community Support Team _Tang

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