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

[Anonymous]

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 

[v-zhangti · Accepted solution]

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]

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 

[amitchandak]

@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]

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]

@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 ))

[Anonymous]

Unfortunately its not working 

I need to return the value of sales for the first month of the year. this value should be a reference value to divide all  other values on it