Forum Discussion

beepboop's avatar
beepboop
Frequent Visitor
2 years ago
Solved

Using PREVIOUSMONTH function to calculate previous month sales, able to filter the dates column

So looking through the syntax of PREVIOUSMONTH(dates) function, you would need to specify a dates column and it would return the previous month based on the first value of the dates column. Problem i...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi beepboop 

     

    Please try this:

    First of all, I create a set of sample data:

    Then create a measure:

    Measure = 
	IF(
		MONTH(TODAY()) - 1 = 0,
		CALCULATE(
			SUM('Table'[value]),
			FILTER(
				ALLSELECTED('Table'),
				MONTH('Table'[date]) = 12 && YEAR('Table'[date]) = YEAR(TODAY() - 1)
			)
		),
		CALCULATE(
			SUM('Table'[value]),
			FILTER(
				ALLSELECTED('Table'),
				MONTH('Table'[date]) = MONTH(TODAY()) - 1 && YEAR('Table'[date]) = YEAR(TODAY())
			)
		)
	)

    The result is as follow:

    Best Regards,

    Zhengdong Xu

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi beepboop 

 

Please try this:

First of all, I create a set of sample data:

Then create a measure:

Measure = 
	IF(
		MONTH(TODAY()) - 1 = 0,
		CALCULATE(
			SUM('Table'[value]),
			FILTER(
				ALLSELECTED('Table'),
				MONTH('Table'[date]) = 12 && YEAR('Table'[date]) = YEAR(TODAY() - 1)
			)
		),
		CALCULATE(
			SUM('Table'[value]),
			FILTER(
				ALLSELECTED('Table'),
				MONTH('Table'[date]) = MONTH(TODAY()) - 1 && YEAR('Table'[date]) = YEAR(TODAY())
			)
		)
	)

The result is as follow:

Best Regards,

Zhengdong Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

beepboop's avatar
beepboop
Frequent Visitor
2 years ago

Is there a way to return only one calculate expression and do an IF expression within the dates itself? Such as CALCULATE(SUM(TABLE)), IF(MONTH()=....,FILTER(DATE)..,FILTER))