Need help comparing Total Value of Expense from Last month vs This Month

[Anonymous]

Hello,

I'm trying to compare Last Month's Total Value against This Month's Total Value in a Clustered Column Chart.

please suggest an easy way to do this. following are the two columns I'm trying to get this done with.

Expense Date "dates in dd/mm/yyyy format"

Amount "value of expense"

Any help would be really appreciated.

[Greg_Deckler · Accepted solution]

So you could create a column like:

Month Legend = 
  VAR __Today = TODAY()
  VAR __ThisMonthMin = DATE(YEAR(__Today),MONTH(__Today),1)
  VAR __ThisMonthMax = EOMONTH(__Today,0)
  VAR __LastMonthMax = EOMONTH(__Today,-1)
  VAR __LastMonthMin = DATE(YEAR(__LastMonthMax),MONTH(__LastMonthMax),1)
RETURN
  SWITCH(TRUE(),
    [Expense Date] >= __ThisMonthMin && [Expense Date] <= __ThisMonthMax,"This Month",
    [Expense Date] >= __LastMonthMin && [Expense Date] <= __LastMonthMax,"Last Month",
    BLANK()
  )
 

Then you could use that in your legend.

[Anonymous]

Dear Greg,

Thank you so much your solution worked wonderfully. Although i don't fully understand what is happening in that Formula just did as you instructed and it works. 

also can you please also edit it for quarterly comparision? i would have done it if i understood it fully 🙂

Thank you again Greg for helping me out this quickly.

[Greg_Deckler]

@Anonymous Quarters are more of a pain in my opinion. You would need a numeric "Quarter" column using QUARTER function. Assuiming that exists and is called Quarter I would then probably do something like this:

Quarter Legend = 
  VAR __Today = TODAY()
  VAR __ThisYear = YEAR(__Today)
  VAR __ThisQuarter = QUARTER(__Today)
  VAR __ThisQuarterMin = MINX(FILTER('Table',YEAR([Expense Date]) = __ThisYear && QUARTER([Expense Date]) = __ThisQuarter),[Expense Date])
  VAR __ThisQuarterMax = MAXX(FILTER('Table',YEAR([Expense Date]) = __ThisYear && QUARTER([Expense Date]) = __ThisQuarter),[Expense Date])
  VAR __LastQuarter = 
    SWITCH(__ThisQuarter,
      1,4,
      2,1,
      3,2,
      3
    )
  VAR __LastQuarterYear = IF(__ThisQuarter <> 4,__ThisYear,__ThisYear - 1)
  VAR __LastQuarterMin = MINX(FILTER('Table',YEAR([Expense Date]) = __LastQuarterYear && QUARTER([Expense Date]) = __LastQuarter),[Expense Date])
  VAR __LastQuarterMax = MAXX(FILTER('Table',YEAR([Expense Date]) = __LastQuarterYear && QUARTER([Expense Date]) = __LastQuarter),[Expense Date])
RETURN
  SWITCH(TRUE(),
    [Expense Date] >= __ThisQuarterMin && [Expense Date] <= __ThisQuarterMax,"This Quarter",
    [Expense Date] >= __LastQuarterMin && [Expense Date] <= __LastQuarterMax,"Last Quarter",
    BLANK()
  )

[Greg_Deckler]

OK, so apparently you don't need a separate QUARTER column, I just implemented it dynamically within the measure.

[Anonymous]

@Greg_Deckler Hello again, Greg can you please help me again I'm trying to make another comparison using the commands you gave as a solution, i want to be able to compare last 4 months in a clustered column chart. Comparison would be like This Month Last Month Second Last Month Third Last Month or in Today's case April, March, February, January. I tried manipulating your commands but was not able to get it working. Would be great if you can help out. Thanks a bunch.

[Greg_Deckler]

Hey @Anonymous - Sorry, coming back up to speed on this, so you want to compare this month to the last four months? Is that correct?

[Anonymous]

Yes Greg, but thanks your explainations above i was able to do this my self. also after doing this i'm facing an issue where in any of the last months data 31st date is not showing. This is not that big of a issue but still couldnt figure our why this is happening also im using Sharepoint as my data source.

This is the DAX im using.

Month Legend =

VAR __Today = TODAY()

VAR __ThisMonthMin = DATE(YEAR(__Today),MONTH(__Today),1)

VAR __ThisMonthMax = EOMONTH(__Today,0)

VAR __LastMonthMax = EOMONTH(__Today,-1)

VAR __LastMonthMin = DATE(YEAR(__LastMonthMax),MONTH(__LastMonthMax),1)

VAR __SecondLastMonthMax = EOMONTH(__Today,-2)

VAR __SecondLastMonthMin = DATE(YEAR(__SecondLastMonthMax),MONTH(__SecondLastMonthMax),1)

VAR __ThirdLastMonthMax = EOMONTH(__Today,-3)

VAR __ThirdLastMonthMin = DATE(YEAR(__ThirdLastMonthMax),MONTH(__ThirdLastMonthMax),1)

RETURN

SWITCH(TRUE(),

[Expense Date] >= __ThisMonthMin && [Expense Date] <= __ThisMonthMax,"This Month",

[Expense Date] >= __LastMonthMin && [Expense Date] <= __LastMonthMax,"Last Month",

[Expense Date] >= __SecondLastMonthMin && [Expense Date] <= __SecondLastMonthMax,"2nd Last Month",

[Expense Date] >= __ThirdLastMonthMin && [Expense Date] <= __ThirdLastMonthMax,"3rd Last Month",

BLANK()

)

I was also thinking of comparing Quarters like Last quarter and the last three quarters but the DAX you gave for Quarters is very complex and im unable to crack it yet.

[Anonymous]

Your Solution for Quarterly Comparsion work wonders again 🙂

Thank you so much Greg and Kudos for for the Best Solution.

[Greg_Deckler]

Sure, @Anonymous let me explain what is going on:

1. Get Today's date using TODAY function
2. Get the first date of the current month using __Today variable using DATE function. DATE function takes 3 arguments, year, month and day so use YEAR to get the year for __Today, MONTH to get the month of __Today and the first day of the month, 1 (__ThisMonthMin)
3. EOMONTH returns the date of the end of the month given a date and how many months forward or backward. So the end of the month for __Today is a month offset of 0 (__ThisMonthMax)
4. For last month, we can use EOMONTH with an offset of -1 to get the last day of last month (__LastMonthMax)
5. To get the first date of last month, we use our DATE trick from before but use __LastMonthMax instead of __Today (__LastMonthMin)
6. RETURN statement we use SWITCH(TRUE()...) This is a special version of the SWITCH statement which allows complex logic and avoids nested IF statements. The first line of the SWITCH statements checks to see if the current row's value for the Expense Date column falls within the range of __ThisMonthMin and __ThisMonthMax. If so, it returns "This Month". The next line similar checks for whether the current row's value for the Expense Date column falls within the range of __LastMonthMin and __LastMonthMax. If so, it returns "Last Month". If none of these conditions are true, it returns BLANK.