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

Community Champion

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

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
2 REPLIES 2
Community Support

Hi @CheenuSing ,

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.

Community Support

Hi @CheenuSing ,

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.