Forum Discussion

inna_sysco's avatar
inna_sysco
Icon for Helper II rankHelper II
5 years ago
Solved

Select START and END date from the ONE column

Hi, community! Maybe it's an easy question, but I haven't found a good answer yet...   HOW can I select START and an END date (2 separate dropdowns) from the ONE column in the Calendar table?  An...
Need Help
  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    Hi, inna_sysco 

    You can create a Measure like the following to get the result you want.

     

    Current Sales =

    VAR start_date =

        CALCULATE ( MIN ( Calendar_start[Date] ), ALLSELECTED ( Calendar_start[Date] ) )

    VAR end_date =

        CALCULATE ( MAX ( Calendar_end[Date] ), ALLSELECTED ( Calendar_end[Date] ) )

    RETURN

        CALCULATE (

            SUM ( 'Table'[VALUE] ),

            'Table'[DAYTIME] >= start_date

                && 'Table'[DAYTIME] <= end_date

    )

     

    The result looks like this:

    Here is the pbix.

     

    Best Regards,

    Caiyun Zheng

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • v-cazheng-msft's avatar
    v-cazheng-msft
    5 years ago

    Hi, inna_sysco 

    You can create a Calculated column and use it as the X axis of the visual.

     

    Format DAYTIME = FORMAT(Table_test[DAYTIME],"mmm")&" "&YEAR(Table_test[DAYTIME])

     

    The result looks like this:

     

    Here is the pbix.

     

    Best Regards

    Caiyun Zheng

     

    Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • inna_sysco's avatar
    inna_sysco
    Icon for Helper II rankHelper II
    5 years ago

    Hi amitchandak , thanks for your answer!
    I tried to implement the solution presented in your article, but it doesn't work for me...
    I used your measure to calculate sum:

    Current Sales = (
VAR _Cuur_start = Min(‘Date’[Date Filer])
VAR _Curr_END = Max(‘Date’[Date Filer])
return
calculate(sum(Sales[Sales Amount]),Sales[Sales Date] >= _Cuur_start && Sales[Sales Date] <= _Curr_END ))

    But in my case, using SELECTEDVALUEs from 2 different slicers:

    Current Sales = (
VAR _Cuur_start = SELECTEDVALUE('Calendar_start'[Date])
VAR _Curr_END = SELECTEDVALUE('Calendar_end'[Date])
    return
calculate(sum(Table[VALUE]), Table[DAYTIME] >= _Cuur_start && Table[DAYTIME] <= _Curr_END ))

    Maybe I did something wrong?