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...
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.

     

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.