Previous month data

Hi ApurvaKhatri,

Use your sample table to test, get expected result.

1. Create a Calendar Table and build a relatioship from the your Fact Table(named Table2 in my formula) to your Calenda Table.

Calendar = CALENDAR(MIN(Table2[As of Date]),MAX(Table2[As of Date]))

Create calculated column to get Year-Month column using the formula.

Year-Month = YEAR('Calendar'[Date])&FORMAT('Calendar'[Date],"MMM")

2. Create measure using your formula below.

Current M1 =
CALCULATE (
    SUM ( Table2[Balance] ),
    FILTER ( Table2, Table2[ Data Segregation] = "Current" )
)

>1 M1 =
CALCULATE (
    SUM ( Table2[Balance] ),
    FILTER ( Table2, Table2[ Data Segregation] = ">1" )
)

>2 M2 =
CALCULATE (
    SUM ( Table2[Balance] ),
    FILTER ( Table2, Table2[ Data Segregation] = ">2" )
)

Previous-Month CurrentM1 = CALCULATE(Table2[Current M1],NEXTMONTH('Calendar'[Date]))

Previous-Month >1M1 = CALCULATE(Table2[>1 M1],NEXTMONTH('Calendar'[Date]))

Previous-Month >2M2 = CALCULATE(Table2[>2 M2],NEXTMONTH('Calendar'[Date]))

Create a table visual, select the Calendar[Year-Month] and all the measure as values level.


Best Regards,
Angelia