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

[CheenuSing]

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

[v-kaiyue-msft]

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.

[v-kaiyue-msft]

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.