Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.