cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MohanV Established Member
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.1.PNG

 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.2.PNG

 

 

 

 

 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

 

 

9 REPLIES 9
Super User
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.

 

 


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


mattbrice Senior Member
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.

MohanV Established Member
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.4.PNGits giving me a wrong result then i changed it into5.PNG

 

 

 

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

thanks for the quick reply.

Its really huge for me.

 

 

 

 

 

MohanV Established Member
Established Member

Re: Need help to write DAX measure

Thanks for the reply @mattbrice.

I did tried what you have suggested.

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

 

Please dont hesitate to correct me. Smiley Happy

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

)

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


MohanV Established Member
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.2.PNG

1.PNG

 

 

 

 

 

mattbrice Senior Member
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.

MohanV Established Member
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
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) )

 

1.PNG

 

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