Need help to write DAX measure

[Anonymous]

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

Please suggest me, it will be so helpful for me.

Thanks,

Mohan V

[mattbrice]

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.

[Anonymous]

Thanks for the reply @mattbrice.

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.

[mattbrice]

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.

[Anonymous]

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

I ll be so thankful to you

[v-shex-msft]

Hi @Anonymous,

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

[Greg_Deckler]

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.

[Anonymous]

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.

thanks for the quick reply.

Its really huge for me.

[Greg_Deckler]

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

)

[Anonymous]

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