Calculate L6M Score and then its Quarter Average

[Anonymous]

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

[amitchandak]

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))  

[Anonymous]

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.

[v-chuncz-msft]

@Anonymous 

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

[Anonymous · Accepted solution]

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.