cancel
Showing results for
Did you mean:
Established Member

## Need help to write DAX measure

Hello All,

I am a newbie to PowerBI and DAX.

I need your valueable suggestions and triks to solve the below query.

I would like to compare the current month(May-2017) Sales value(40) with previous month(Apr-2017) sales value(23).

And accoring to the comarision i would like to add another column which shows message as less than or greater than previous month.

But if previous month sales value is not avaible then it should comapre with the previous month sales value.

Like as above image the previous month value is not avaibel then I would like to comapre with THE previous month sales value(Mar-2017).

Thanks,

Mohan V

9 REPLIES 9
Super User

## Re: Need help to write DAX measure

If you had a column like:

`YearMonth = VALUE(CONCATENATE(YEAR([Month]),MONTH([Month])))`

Then you should be able to do something like:

```var CurrentYearMonth = VALUE(CONCATENATE(YEAR([TODAY]),MONTH([TODAY])))

IF(ISNULL(CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth-1))),

CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth)) - CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth-2)),

CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth)) - CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth-1))

)```

A similar formula doing a comparison would give you an indicator.

Proud to be a Datanaut!

Senior Member

## Re: Need help to write DAX measure

If you have a separate calendar table, this is how i would approach the problem:

`Total Sales = Sum ( Table[Sales] )`

and then

```=
VAR Sales_this_Month = [Total Sales]
VAR Month_Last_Sales =
CALCULATE (
LASTNONBLANK ( Calendar[Date], [Total Sales] ),
FILTER (
ALL ( Calendar ),
Calendar[Date] <= ENDOFMONTH ( PREVIOUSMONTH ( MAX ( Calendar[Date] ) ) )
)
)
VAR Sales_Previous_Month =
CALCULATE ( [Total Sales], PARALLELPERIOD ( Month_Last_Sales, 0, MONTH ) )
RETURN
Sales_this_Month - Sales_Previous_Month```

This figures out the last date you had sales, no matter how many months prior.  May be another option to try.

Established Member

## Re: Need help to write DAX measure

Wow that was huge @Greg_Deckler.

I didnt expected that.

But while I am trying this i got some issues here.its giving me a wrong result then i changed it into

But then When i tried the next one, theres lots of errors.

Its really huge for me.

Established Member

## Re: Need help to write DAX measure

I did tried what you have suggested.

But i got struck at Here table name is Actual, and it is having columns as Date(Perfect date format), Actual( i.e Sales )

Please dont hesitate to correct me.

Super User

## Re: Need help to write DAX measure

Sorry, that's what I get when trying to write DAX without testing.

Try this:

```MyMeasure = var CurrentYearMonth = VALUE(CONCATENATE(YEAR(TODAY()),MONTH(TODAY())))

RETURN IF(ISBLANK(CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth-1))),

CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth)) - CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth-2)),

CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth)) - CALCULATE(SUM([Sales]),FILTER(Sales,[YearMonth]=CurrentYearMonth-1))

)```

Proud to be a Datanaut!

Established Member

## Re: Need help to write DAX measure

@Greg_Deckler I tried the new one but i dont see any result as in below image.

Senior Member

## Re: Need help to write DAX measure

Well same here.  That's what i get for freehanding Dax without testing.  I look at it closer tomorrow if I have time.  Sorry for the false lead.

Established Member

## Re: Need help to write DAX measure

@mattbrice@Greg_Deckler can you please give any suggestions..

I ll be so thankful to you

Community Support Team

## Re: Need help to write DAX measure

Hi @MohanV,

You can refer to below steps if it suitable for your requirement:

1. Add calcualte column to convert "month year" column to date.

`Date = DATEVALUE([Year Month])`

2. Add calculate column to compare with previoust record.

```Compare with Prev =
var previous=LOOKUPVALUE('Table 2'[Total],'Table 2'[Date],MAXX(FILTER(ALL('Table 2'),'Table 2'[Date]<EARLIER('Table 2'[Date])),[Date]))
return
IF([Total]>previous,"greater","less") ```

3. Add a calculated column to show the compare result.

```Result =
var prev=MAXX(FILTER(ALL('Table 2'),'Table 2'[Date]<EARLIER('Table 2'[Date])),[Date])
var prev2=MAXX(FILTER(ALL('Table 2'),'Table 2'[Date]<prev),[Date])
return
IF([Compare with Prev]="Less",LOOKUPVALUE('Table 2'[Total],'Table 2'[Date],prev),LOOKUPVALUE('Table 2'[Total],'Table 2'[Date],prev2) )```

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |