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.

     

v-cazheng-msft's avatar
v-cazheng-msft
Icon for Community Support rankCommunity Support
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.

 

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

    Hi v-cazheng-msft !
    Thank you so much for the detailed explanation! 
    Your solution works great when I need to display the total on the card visual, but when I need to also manage the timeline it doesn't work...

    Could you help me with that?
    I am attaching the file where I tried to apply your solution.


    • v-cazheng-msft's avatar
      v-cazheng-msft
      Icon for Community Support rankCommunity Support
      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

        Thank you, v-cazheng-msft !
        This works perfectly!
        I very much appreciate your help!