Is there a way to create measure using variable to return last month Base size in power BI or DAX

Question

I'm creating a report in Power BI, and want to return the last month Size.

I have a table with 4 columns named as Name, Size, Connections, Disconnections. The values on these columns are for the last 12 months. For example, Name column has A, B, C; Size column has 3608445, 2839945,874434; Connections column has 66875,85632,19237 and Disconnections column has 52658,61529 and 15832 values. These values are for the last 12 months. See screenshot below.

Actual and Expected table

The code I used to created the expected table is

last_month_size =
VAR current_month =
    MONTH ( TODAY () )
RETURN
    CALCULATE (
        [Size],
        FILTER (
            'Monthly Calendar_Lookup',
            MONTH ( 'Monthly Calendar_Lookup'[Dates] ) = current_month - 1
        )
    )

I want to create a measure that will return last month Size column but the Connections and Disconnections remains the same. For example, the Size value changes while the connections and disconnections values remains the last 12 month values. I find it difficult because the columns are on the same table.

Eal Eal · Accepted Answer · 2019-04-24T08:39:25

I have researched about the question I have posted and I have found a solution. This solution to the problem is creating measures and not using variables.

First, I created a measure called Total Size

Total Size =  Sum ( Tablename [Size] )

Then, created another measure called prev_month size using DATEADD function with number_of_intervals as 0

prev_month size = CALCULATE ( [Total Size], DATEADD ('Monthly Calendar_Lookup'[Dates], 0, MONTH ) )

Next, I created measures of total connections and total disconnections

Total Connections =  Sum ( Tablename [Connections] )
Total Disconnections =  Sum ( Tablename [Disconnections] )

Also, I created two measures of rolling 12 months Connections and Disconnections each. Rolling_Connections_12_months = CALCULATE ( SUMX ('Tablename', [Total Connections] ), DATESINPERIOD ('Date'[Month], LASTDATE ( 'Date'[Month] ), -12, MONTH ) )

Rolling_Disconnections_12_months = CALCULATE ( SUMX ('Tablename', [Total Disconnections] ), DATESINPERIOD ('Date'[Month], LASTDATE ( 'Date'[Month] ), -12, MONTH ) )

Drag the Name, prev_month size, Rolling_Connections_12_months, and Rolling_Disconnections_12_months on the canvas as a table visualization.

Then finally, I drag a relative Date slicer and set it as Last 1 Month. This produces the expected results

Is there a way to create measure using variable to return last month Base size in power BI or DAX

1 Answers