Skip to main content
cancel
Showing results for 
Search instead 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

Reply
CheenuSing
Community Champion
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
v-kaiyue-msft
Community Support
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.

v-kaiyue-msft
Community Support
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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.