Forum Discussion

bolabuga's avatar
bolabuga
Helper V
9 years ago
Solved

Measure 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)
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    bolabuga

     

    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

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
9 years ago

bolabuga

 

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

  • bolabuga's avatar
    bolabuga
    Helper V
    9 years ago

    Thks herbert, that was just what i needed :).