Forum Discussion

sarfkermali's avatar
sarfkermali
Frequent Visitor
8 years ago
Solved

Calculating Month to Month Change.

Hi Everyone, i have a table as follows:   Location Type date # people calgary Big march, 2017 1143 toronto Small march, 2017 242 california Small march, 2017 609 mogadish...
  • v-yuta-msft's avatar
    v-yuta-msft
    8 years ago

    Hi sarfkermali,

     

    Based on data you have provided, it's impossible to calculate change between current month and previous month. If you want to calculate change between current month and two month agos, modify DAX as below:

    Change By Month = 
VAR Previous_Date_Condition =  EOMONTH (MAX(Table1[date]), -3 ) + 1
VAR Current_Month =
    CALCULATE (
        SUM ( Table1[# people] ),
        ALLEXCEPT ( Table1, Table1[Location], Table1[Type] )
    )
VAR Previous_Month = 
    CALCULATE (
        SUM ( Table1[# people] ),
        FILTER(ALLEXCEPT ( Table1, Table1[Location], Table1[Type] ), Table1[date] = Previous_Date_Condition)
    )
RETURN
    ( Current_Month - Previous_Month )
        / Previous_Month

     

    PBIX here: https://www.dropbox.com/s/t1zlz7ijxkwhem1/Calculating%20Month%20to%20Month%20Change..pbix?dl=0 

     

    Regards,

    Jimmy Tao

v-yuta-msft's avatar
v-yuta-msft
Icon for Community Support rankCommunity Support
8 years ago

Hi sarfkermali,

 

Create a measure and try DAX below:

Change By Month =
VAR Current_Month =
    CALCULATE (
        SUM ( Table1[# people] ),
        ALLEXCEPT ( Table1, Table1[Location], Table1[Type] )
    )
VAR Previous_Month =
    CALCULATE (
        SUM ( Table1[# people] ),
        ALLEXCEPT ( Table1, Table1[Location], Table1[Type] ),
        FILTER ( Table1, Table1[date] = EOMONTH ( Table1[date], -2 ) + 1 )
    )
RETURN
    ( Current_Month - Previous_Month )
        / Previous_Month

Regards,

Jimmy Tao