cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
arya4it Frequent Visitor
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:

L6M Calculation.png

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.

L6M Calculation Table View.png

 

 

 

 

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
arya4it Frequent Visitor
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):

L6MBridge.png

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

View solution in original post

4 REPLIES 4
Super User IV
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))  

 

 





Did I answer your question? Mark my post as a solution!
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


arya4it Frequent Visitor
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
Community Support Team

Re: Calculate L6M Score and then its Quarter Average

@arya4it 

 

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.
arya4it Frequent Visitor
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):

L6MBridge.png

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

View solution in original post

Helpful resources

Announcements
Coming Soon: T-Shirt Design Contest

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!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors
Top Kudoed Authors