cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Previous Month Filtering

Hello All,

I have been stuck on this issue for a few hours and really can't seem to fix it. Any help would be greatly appreciated.

DateDim Table

Deals Table

I have a measure called "Average Cost" which is the Total Cost / Total number of deals. This measure works as expected and can be filtered by whichever month has been selected. My issue is when I try to create a measure for Average cost Last month.  To do this I have created a measure called PreviousDate

PreviousDate measure - This value is generated depending on whatever is selected in the filter. For example I have currently selected May 2022, so the measure shows 2022-04

I am then trying to filter the Deals table, where the YearMonth Column = Previous Month measure. See below

What I have tried

I did initially try the formula
Average Cost Last Month = CALCULATE([Average Cost], DateDim[YearMonth] = [PreviousDate])

But I am Given This error

After researching this, I believe the work around was to use all function & filter function, so I produced this

Average Cost Last Month =

CALCULATE([Average Cost],

all('DateDim'[YearMonth]), filter(DateDim, DateDim[YearMonth] = [PreviousDate])

)

But this just returns a blank value. I need the AVERAGE COST MEASURE but only for values that are in the PREVIOUSMONTH to the current month selected

Any help would be massively appreciated!

-

1 ACCEPTED SOLUTION
Super User

Hi,

Average Cost Last Month =
VAR _prevdate = [PreviousDate]
RETURN
CALCULATE (
[Average Cost],
FILTER ( ALL ( DateDim ), DateDim[YearMonth] = _prevdate )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Super User

Hi,

Average Cost Last Month =
VAR _prevdate = [PreviousDate]
RETURN
CALCULATE (
[Average Cost],
FILTER ( ALL ( DateDim ), DateDim[YearMonth] = _prevdate )
)

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Helper I

Worked great thank you!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.