Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Divide all dates values on a a specific date value from a norther column

Hi all 

I have the followeing table: 

I would like to divide all returned values  from the 4th column on only the sales from January 

How could I achieve that ? 

Thanks in advance

Regards 

 

Tips and Tricks
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    If you try to solve it with calculated columns, it will be easy.

     

    Sales_January =
CALCULATE (
    MAX ( 'Table'[Sales] ),
    FILTER (
        'Table',
        [Date].[MonthNo] = 1
            && [Date].[Year] = EARLIER ( 'Table'[Date].[Year] )
    )
)

    %Returned/January = DIVIDE([Returned],[Sales_January])

     

     

    Best Regards,

    Community Support Team _Charlotte

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

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    Anonymous ,

    Either use an independent date slicer and select jan there
    // Independent Date table - Date1 and Joined Table Date
    measure =
    var _max = maxx(allselected('Date1'),'Date1'[Date]) // Use Date(2021,01,31)
    var _min = minx(allselected('Date1'),'Date1'[Date])
    return
    CALCULATE(SUM(Sales[Sales Amount]),filter(Date, Date[Date]>=_min && Date[Date] <=_max ))

     

    or put date


    measure =
    var _max = Date(2021,01,31)
    var _min = Date(2021,01,01)
    return
    CALCULATE(SUM(Sales[Sales Amount]),filter(Date, Date[Date]>=_min && Date[Date] <=_max ))

     

    You can also Table's Date in place Date[Date]

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you for the fast reply 

      I think I misexplained my problem 

      I want to divide all the months returned value on the sales resulted from first month of the year 

      The idea filling all the values in yellow so I can build monthly trend

      Thanks again and looking forward for your help 

      REgards 

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        4 years ago

        Anonymous , Use all in filter and try

         

        measure =
        var _max = Date(2021,01,31)
        var _min = Date(2021,01,01)
        return
        CALCULATE(SUM(Sales[Sales Amount]),filter(all(Date) , Date[Date]>=_min && Date[Date] <=_max ))

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi, Anonymous 

     

    If you try to solve it with calculated columns, it will be easy.

     

    Sales_January =
CALCULATE (
    MAX ( 'Table'[Sales] ),
    FILTER (
        'Table',
        [Date].[MonthNo] = 1
            && [Date].[Year] = EARLIER ( 'Table'[Date].[Year] )
    )
)

    %Returned/January = DIVIDE([Returned],[Sales_January])

     

     

    Best Regards,

    Community Support Team _Charlotte

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    I need to divide all values on the red boxes on the value of the first month of the their year 

    When I apply divide (Returned, Sales January) it only divided the first month and don't divide the other months. 

    In OTher words, sales January is a measure where it calculate the sales when month = "January" , now I want to fill down all the missing data . in the third column ('Slaes January') so I can divided on the 4th column to calculate the ratio