Forum Discussion

ctaishah's avatar
ctaishah
Frequent Visitor
3 years ago
Solved

Previous month doesn't work

Hi I am new to powerbi
I need to create a report having previous month.

The Total Expenses is aggregated for 1 month. I do not have an everyday date and value.

 

Output expected

What I get is all the value will be in the same month. 

 

How do i make them reflect as a previous month?

I use 3 different functions but none of them works. The dax that i use are as below

1. 

Last Month Expenses = CALCULATE(SUM('BIDWH BFIN_FACT_GL_ACCOUNT'[ACCOUNT_AMOUNT]), PARALLELPERIOD('BIDWH BC_DIM_BFIN_FISCAL_PERIOD'[START_DATE],-1,MONTH))

 

2. 

Last Month Expenses1 = CALCULATE(sum('BIDWH BFIN_FACT_GL_ACCOUNT'[ACCOUNT_AMOUNT]),PREVIOUSMONTH('BIDWH BC_DIM_BFIN_FISCAL_PERIOD'[START_DATE]))

 

3. 

Last Month Expenses2 = CALCULATE(SUM('BIDWH BFIN_FACT_GL_ACCOUNT'[ACCOUNT_AMOUNT]),DATEADD('BIDWH BC_DIM_BFIN_FISCAL_PERIOD'[START_DATE],-1,MONTH))

 

Any idea on how to resolve this?

 

Thank you

Need Help
  • NaveenGandhi's avatar
    NaveenGandhi
    3 years ago

    Hello ctaishah 

     

    All you need to perform pervious month operation is 1 date falling on each month and a date table, If you do not have a date column as below, Create a date for 1st of each month by doing below steps, else skip to step 3.

     

    1. Create a Month number from month name using this Measure.

    MonthNo = SWITCH('Table'[Month],
            "January", 1,
            "February",2,
            "March",3,
            "April",4,
            "May",5,
            "June",6,
            "July",7,
            "August",8,
            "September",9,
            "October",10,
            "November",11,
            "December",12
                    )

    2. Then Create a Date column using year, month no using below measure.
    Date = DATE('Table'[Year],'Table'[MonthNo],1)
     

     

    3. Finally create a date table and create a relationship between date table and total sales table.
    Date = CALENDAR(min('Table'[Date]),max('Table'[Date])) 
     
    4. Create the Last month expenses below and use it in the table visual
    Last_Month_Expenses = CALCULATE(sum('Table'[Total Expenses]),PREVIOUSMONTH('Date'[Date]))
     

     

    If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

     

1 Reply

  • NaveenGandhi's avatar
    NaveenGandhi
    Memorable Member
    3 years ago

    Hello ctaishah 

     

    All you need to perform pervious month operation is 1 date falling on each month and a date table, If you do not have a date column as below, Create a date for 1st of each month by doing below steps, else skip to step 3.

     

    1. Create a Month number from month name using this Measure.

    MonthNo = SWITCH('Table'[Month],
            "January", 1,
            "February",2,
            "March",3,
            "April",4,
            "May",5,
            "June",6,
            "July",7,
            "August",8,
            "September",9,
            "October",10,
            "November",11,
            "December",12
                    )

    2. Then Create a Date column using year, month no using below measure.
    Date = DATE('Table'[Year],'Table'[MonthNo],1)
     

     

    3. Finally create a date table and create a relationship between date table and total sales table.
    Date = CALENDAR(min('Table'[Date]),max('Table'[Date])) 
     
    4. Create the Last month expenses below and use it in the table visual
    Last_Month_Expenses = CALCULATE(sum('Table'[Total Expenses]),PREVIOUSMONTH('Date'[Date]))
     

     

    If this post helps, then please consider Accept it as the solution to help the others find it more quickly.