topic Re: Current Month, Previous Month and In Prior 3 months to current Month in DAX Commands and Tips

Current Month, Previous Month and In Prior 3 months to current Month

Anonymous — Wed, 12 Jun 2024 16:09:20 GMT

Hi All,

I have Fact Data which has ticketno, stepnumber, score.
I have Fact Ticket which has ticketno, executive, ClosureDate.

Table1 and Table2 are related on ticketno - many to one

Table2 is related to dimdate on date->closedate - one to many.

I want to calculate the average of score from table1 by executive from filtered table 2 on closedate

I select a month from dimdate. Example - May 24
for current Month - May 24

for previous Month - Apr 24

for Prior 3 Months period - Jan, Feb, Mar 24

expected output

CurMonth Prev Month- Apr 24 Prior 3 Months - Jan-Mar 24

Exec 1 2.5 3.8
Exec 2 4.2 3.75
Exec 3 4.5 4.25

Measuers Built
1. CurrentMonth = ( this is working)

VAR SelectedMonth = SELECTEDVALUE('Dim Date'[MonthYear])

RETURN

CALCULATE(

AVERAGEX(

'Fact Data',

'Fact Data'[Score]

FILTER(

'Fact Ticket',

'Fact Ticket'[Closure Date] IN

CALCULATETABLE(

VALUES('Dim Date'[Date]),

'Dim Date'[MonthYear] = SelectedMonth

)

2. AverageScorePreviousMonth = ( this is working )

CALCULATE(

[AverageScore],

PREVIOUSMONTH('Dim Date'[Date])

)

3. Prior 3 Month Average Ratings = ( not working)

VAR MaxDate = MAX('Dim Date'[CurMonthOffset])

RETURN

CALCULATE(

[AverageScore],

REMOVEFILTERS('Dim Date'),

FILTER(

'Dim Date',

'Dim Date'[CurMonthOffset] >= MaxDate - 4 && 'Dim Date'[CurMonthOffset] <= MaxDate -2

)

Appreciate any help

Cheers

CheenuSing

Re: Current Month, Previous Month and In Prior 3 months to current Month

Anonymous — Thu, 13 Jun 2024 05:22:07 GMT

Hi Anonymous ,

You can try this expression.

Prior3MonthAverageRatings = VAR SelectedMonthOffset = SELECTEDVALUE('Dim Date'[CurMonthOffset]) VAR StartOffset = SelectedMonthOffset - 4 VAR EndOffset = SelectedMonthOffset - 2 RETURN CALCULATE( [AverageScore], REMOVEFILTERS('Dim Date'), FILTER( 'Dim Date', 'Dim Date'[CurMonthOffset] > StartOffset && 'Dim Date'[CurMonthOffset] <= EndOffset ) )

If you don't get the results you expect, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

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

Re: Current Month, Previous Month and In Prior 3 months to current Month

Anonymous — Wed, 26 Jun 2024 06:59:49 GMT

Hi Anonymous ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster? Thank you very much for your kind cooperation!

Best Regards,

Clara Gong

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