Calculated column query problem

[AviratG]

Hi, I have the following data structure.

I need a calculated column which can be calculated by dividing the following cells marked in red i.e. 1-30(as of Dec 1,2016) and 31-60(as of Jan 1,2017) This calculation also needs to be done for all the rows in the two column.

Similarly, we have to calculate another column which has the division for 31-60(as of Dec 1,2016) and 61-90(as of Jan 1,2017) and so on similar to the previous column

This needs to be done for multiple Business Units which are not captured in the screenshot.

Would appreciate your inputs!

[Ashish_Mathur]

Hi,

My suggestion would be to unpivot the data and convert it into a 4 column one with columns for Business Unit, Month, ageing buckets and values.  Then we can directly write measures to get your desired result.

[Phil_Seamark · Accepted solution]

HI @AviratG

This might get you gonig on the right path

New Col = 
VAR LastMonth1_30 = 
    MINX(
        filter(
            'Table',
            'Table'[Month] = EDATE(EARLIER('Table'[Month]),-1)
            ),
            'Table'[1-30])
VAR ThisMonth31_60 = 'Table'[31-60]
RETURN DIVIDE(ThisMonth31_60,LastMonth1_30)