cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Calculate L6M Score and then its Quarter Average

Hi,

I am trying to calcualte a L6M(Last 6 Monts) Score:

KPI_Store Purchase Freq (L6M) =
SUMX(
SUMMARIZE(
factSO
,factSO[StartOfMonth]
,"InvoiceCount"
,CALCULATE([BIT_Invoice Count (Distinct)],ALLSELECTED(factSO[StartOfMonth]),DATESINPERIOD(dimCalendar[Date], EOMONTH(MAX(factSO[StartOfMonth]),0),-6,MONTH))
,"CustomerCount"
,CALCULATE([BIT_Customer Count (Distinct)],ALLSELECTED(factSO[StartOfMonth]),DATESINPERIOD(dimCalendar[Date], EOMONTH(MAX(factSO[StartOfMonth]),0),-6,MONTH))
), DIVIDE([InvoiceCount] ,[CustomerCount] ,0)
)

If i check this sumarize as table it gives me corect numbers i.e. final score for a month would be InvoiceCount/CustomerCount which will be the result for SUMX:

But when i am trying to plot the Score(in a matrix/table) against the Months from Calender table it filters the data itself for the specific Month and calcualtes the score according to that filtered data i.e. one month.

This is my problem statement:

1) I want to show L6M score for all the months

2) I also have to calculate average scores for quarters as well e.g. (Dec Score[Data: July to Dec] + Nov Score[June to Nov] + Oct Score[May to Oct])/3 and show under 1 column e.g. Q4

Help me out.

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

## Re: Calculate L6M Score and then its Quarter Average

Thanks for the input guys.

Found a solution by creating the Bridge Table (kind of cartesian):

and connected Date_Calender to calender date and Date_FactData to fact data date.

4 REPLIES 4
Super User IV

## Re: Calculate L6M Score and then its Quarter Average

I did not get it completely. But can remove max inside the EOMONTH and try like

``Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH))  ``

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Frequent Visitor

## Re: Calculate L6M Score and then its Quarter Average

for Monthly Value:

Without removeing MAX from the measure, if i am ploting the L6M Measure against the date(i.e. First Date of Month) from fact table it is working fine if the date ascending. but if i am sorting the date descending then the measure value is using only one month date for L6M Measure calculation.

Community Support Team

## Re: Calculate L6M Score and then its Quarter Average

You may check if the following post helps.

https://community.powerbi.com/t5/Desktop/Sort-By-Column-changes-the-Measure-results/m-p/91258#M38515

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Calculate L6M Score and then its Quarter Average

Thanks for the input guys.

Found a solution by creating the Bridge Table (kind of cartesian):

and connected Date_Calender to calender date and Date_FactData to fact data date.

Announcements

#### Coming Soon: T-Shirt Design Contest

Keep your eyes open for our upcoming T-shirt design contest!

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!