Forum Discussion

Happy2023_05's avatar
Happy2023_05
Icon for Helper I rankHelper I
3 years ago
Solved

Calculating values based on Date selection from slicer

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? 

DAX help needed
  • v-xiaotang's avatar
    v-xiaotang
    3 years ago

    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.

2 Replies

  • serpiva64's avatar
    serpiva64
    Icon for Solution Sage rankSolution Sage
    3 years ago

    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.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    3 years ago

    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.