Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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

 

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

 

Thanks,

Mohan V

 

 

Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

9 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        9 years ago

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

)
  • mattbrice's avatar
    mattbrice
    Icon for Solution Sage rankSolution Sage
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

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

      • mattbrice's avatar
        mattbrice
        Icon for Solution Sage rankSolution Sage
        9 years ago

        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.