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) wi...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        mattbrice, Greg_Deckler can you please give any suggestions..

        I ll be so thankful to you