Forum Discussion
compare current month vs Previous month
Hi guys,
i am new to power bi and i want to compare current month sales with last month. i am having data from 2017 january to 2019 november. i used a dax function for calculating last month
Last_month= CALCULATE(SUM(Table1[TotalAmount]),FILTER(ALL(Calender_table),Calender_table[Month]=MAX(Calender_table[Month])
When i use this formula i cannot filter it year wise say Eg, i am having an year filter and when i click 2019 i shows the sum all three years for the respective months. It is not showing the month values for 2019. please help me with it and provide a solution
4 Replies
- tex628
Community Champion
The easiest way to do this is to create a numeric index for your combination of year and month:
Date Y/M Index 2019-01-01 jan-19 1 2019-02-02 feb-19 2 2019-02-03 feb-19 2 2019-03-01 mar-19 3 2019-04-04 apr-19 4
Then reference the previous index in the calculation.
Assuming that the current date is 2019-04, the following will return the index "4":Previous month = Calulate( SELECTEDVALUE( Calendar[Index] ); Calendar[Date] = TODAY() )
Then you can simply use that to calculate the previous index:Last_month = CALCULATE( SUM(Table1[TotalAmount]); Calendar[Index] = [Previous month] -1 )
