cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## 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)

1 ACCEPTED SOLUTION
Microsoft Employee

@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

3 REPLIES 3
Microsoft Employee

@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

Helper V

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

Super User

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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors