Forum Discussion
Helper VMeasure Doubt.
Hello
i would like to create a measure that do the following:
MEASURE = (MONTH) - (LASTMONTH)
The idea is to use the variance to make a waterfall chart showing 2 years (24 months)
You can try with PREVIOUSMONTH function as smoupre said. Just give a simple sample as below. Assuming we have a sales table from 1/1/2014 to one day in 2017.
We can create a Calendar table firstly with following DAX formula. Create relationship with the sales table with date key. Put this Calendar[Date] column into the Slicer visual.
Calendar = CALENDAR ( "1/1/2014", "12/31/2017" )Create a PreMonthSales and a Diff measure. Drag them into the Waterfall chart.
PreMonthSales = CALCULATE ( SUM ( Table1[Sales] ), PREVIOUSMONTH ( Table1[Date] ) )
MEASURE = CALCULATE ( SUM ( Table1[Sales] ) - [PreMonthSales] )
I've also uploaded the PBIX file here for reference.
Best Regards,
Herbert
3 Replies
- Greg_Deckler
Community Champion
Sounds like you need the PREVIOUSMONTH function:
https://msdn.microsoft.com/en-us/library/ee634758.aspx
Can't really say much more without sample data.
v-haibl-msftMicrosoft Employee
You can try with PREVIOUSMONTH function as smoupre said. Just give a simple sample as below. Assuming we have a sales table from 1/1/2014 to one day in 2017.
We can create a Calendar table firstly with following DAX formula. Create relationship with the sales table with date key. Put this Calendar[Date] column into the Slicer visual.
Calendar = CALENDAR ( "1/1/2014", "12/31/2017" )Create a PreMonthSales and a Diff measure. Drag them into the Waterfall chart.
PreMonthSales = CALCULATE ( SUM ( Table1[Sales] ), PREVIOUSMONTH ( Table1[Date] ) )
MEASURE = CALCULATE ( SUM ( Table1[Sales] ) - [PreMonthSales] )
I've also uploaded the PBIX file here for reference.
Best Regards,
Herbert
bolabugaThks herbert, that was just what i needed :).
