Forum Discussion

BratKat's avatar
BratKat
Microsoft Employee
3 years ago
Solved

Concatenate columns when calculating

Creating a Slicer for weeks and this works if I only use DATE 

Working 

Slicer2 =
var _start_date = CALCULATE(MIN('Work Items'[Team Date 01].[Date]),FILTER('Work Items','Work Items'[weeknum]=EARLIER('Work Items'[weeknum])))

var _end_date = CALCULATE(MAX('Work Items'[Team Date 01].[Date]),FILTER('Work Items','Work Items'[weeknum]=EARLIER('Work Items'[weeknum])))
return
_start_date &" - "& _end_date

 

trying to add Year to this column but throws a error, not sure if I can concatenate. 

 

Slicer2 =
var _start_date = CALCULATE(MIN('Work Items'[Team Date 01].[Year]) & "" & ('Work Items'[Team Date 01].[Date]),FILTER('Work Items','Work Items'[weeknum]=EARLIER('Work Items'[weeknum])))

var _end_date = CALCULATE(MAX('Work Items'[Team Date 01].[Year]) & "" & ('Work Items'[Team Date 01].[Date]),FILTER('Work Items','Work Items'[weeknum]=EARLIER('Work Items'[weeknum])))
return
_start_date &" - "& _end_date

 

Thank you 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi BratKat ,

    Is it possible to try to bring up the year?

    Like this:

    Fiscal Quarter =
VAR _1 =
    IF (
        'Work Items'[Quarters] = 3,
        1,
        IF (
            'Work Items'[Quarters] = 4,
            2,
            IF ( 'Work Items'[Quarters] = 1, 3, IF ( 'Work Items'[Quarters] = 2, 4 ) )
        )
    )
VAR _2 =
    YEAR ( table[date] )
RETURN
    _2 & " " & _1

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

4 Replies

  • BratKat's avatar
    BratKat
    Microsoft Employee
    3 years ago

    The slicer returned works however there is no year listed only the Fiscal quarter and our data starts last October, so trying to denote which year the quarter is showing. 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi BratKat ,

    Please have a try.

    Slicer2___ =
VAR _start_date =
    CALCULATE (
        MIN ( 'Work Items'[Team Date 01].[Year] ) & ""
            & MIN ( 'Work Items'[Team Date 01].[Date] ),
        FILTER (
            'Work Items',
            'Work Items'[weeknum] = EARLIER ( 'Work Items'[weeknum] )
        )
    )
VAR _end_date =
    CALCULATE (
        MAX ( 'Work Items'[Team Date 01].[Year] ) & ""
            & MAX ( 'Work Items'[Team Date 01].[Date] ),
        FILTER (
            'Work Items',
            'Work Items'[weeknum] = EARLIER ( 'Work Items'[weeknum] )
        )
    )
RETURN
    _start_date & " - " & _end_date

    If it still does not help, please provide you sample data about [Team Date 01].

     

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Rongtie

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

    • BratKat's avatar
      BratKat
      Microsoft Employee
      3 years ago

      I actually used the incorrect column code for what am trying to achive. 

      This is the actual column code using 

      Fiscal Quarter = IF('Work Items'[Quarters]=3,1, IF('Work Items'[Quarters]=4,2, IF('Work Items'[Quarters]=1,3, IF('Work Items'[Quarters]=2,4))))
       
      This works as I showed in the screenshot I provided prevously. 

      Just trying to add the Year along wih the Quarter number as quarter 2 is 2022 - our fiscal quaters start in July, sorry about the mix up on my end. 

       

       
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi BratKat ,

        Is it possible to try to bring up the year?

        Like this:

        Fiscal Quarter =
VAR _1 =
    IF (
        'Work Items'[Quarters] = 3,
        1,
        IF (
            'Work Items'[Quarters] = 4,
            2,
            IF ( 'Work Items'[Quarters] = 1, 3, IF ( 'Work Items'[Quarters] = 2, 4 ) )
        )
    )
VAR _2 =
    YEAR ( table[date] )
RETURN
    _2 & " " & _1

         

        How to Get Your Question Answered Quickly 

         

        If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

         

        Best Regards
        Community Support Team _ Rongtie

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