Forum Discussion
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...
Greg_Deckler
Community Champion
Community ChampionIf 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.
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)) )