Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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.
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))
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.
@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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |