Dynamic Frequency Value Part 2 (following question)

[Anonymous]

Hi Experts

he is the link to the first question i posted which gives me the correct results.

https://community.powerbi.com/t5/Desktop/creating-a-dynamic-table-in-Power-BI/td-p/753192

I want to be ble to get the frequency as shown below (see image) using the date and Material ID columns.

Sample file is availble at the location of the first question.

https://community.powerbi.com/t5/Desktop/creating-a-dynamic-table-in-Power-BI/m-p/754431#M363568

[dax]

Hi Route217,

The link of your sample in your first link is expired, so I suggest you could upload your sample in this post, then we could help you more correctly.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

[Anonymous]

hi Zoe

Apologies here is the link to the original dataset.

https://www.dropbox.com/s/bpvte6ms66wv8u5/apples.pbix?dl=0

[Anonymous]

This formula work prefectly to get the 84 as shown in the image at the start of the question. BUT my issues is how do i apply this method to my data set.

__freq = 
VAR __table = SUMMARIZE(MK,[Value],"__ties",COUNT(MK[ID])-1)
VAR __table1 = ADDCOLUMNS(__table,"__freq",IF([__ties]=0,0,[__ties]*([__ties]+1)*(2*[__ties]+7)))
RETURN
SUMX(__table1,[__freq])

[Anonymous · Accepted solution]

This is the how the previous measure was worked out and it is spot on 100% dynamically. I am having trouble using the Material_ID and the Dates in the mesure as shown in prevous posting..

__s = 
VAR ComplaintsByFiscalMo = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)))
RETURN
SUMX(ComplaintsByFiscalMo, 
    SUMX(ComplaintsByFiscalMo,
        IF([FISCAL_MON_START_DT]>EARLIER([FISCAL_MON_START_DT])&&[CountComplaints]>EARLIER([CountComplaints]), 1,
        IF([FISCAL_MON_START_DT]>EARLIER([FISCAL_MON_START_DT])&&[CountComplaints]<EARLIER([CountComplaints]), -1))
    )
)+0

[Cmcmahan]

So I guess I don't understand your question.  Are you just trying to get the frequency total (P19 in the image) for a single/smaller selection of MATERIAL_IDs/dates?  If you slice your data, (by dates, by MATERIAL_ID, whatever) it should update the statistics with whatever data is available.  By default, you have your report filtered by ROLL12_COMPLETE_PDS_IND, so your values match the pre-calculated ones.

If it's helpful, here's the measure I used to get the total in P19 in the report last time:

P19 = 
VAR DuplicateTotals = GROUPBY(GROUPBY(PMS_COMPLAINT, PMS_COMPLAINT[FISCAL_MON_START_DT],"CountComplaints", COUNTX(CURRENTGROUP(),1)), [CountComplaints],"Dupe Count", COUNTX(CURRENTGROUP(),1))
RETURN
SUMX( DuplicateTotals, ([Dupe Count]-1) * ([Dupe Count]) * (2*([Dupe Count]-1)+7))

[Anonymous]

the dax is spot on an gives the correct end result.

The only problem i can see with the whole Mann Kandell calculation for __s and __freq is when you have blank data points. 

In R you have to input 12 data point for 12 months and months with not data are shown as 0 not blank. So how do you get around this in DAX.

R Input top part of screen and results bottom part.

Power BI Issues to over come

The Mann kandell falls apart when you have less then 12 data points. if we have 12 data points all the values are 100% spot on with R. the answer lies in getting 0 into the table. for that measure. 

[Cmcmahan]

If you want a measure to return zero instead of blank by default, you can just add +0 to the end of the expression.

[Anonymous]

Don't work...it mess up other measure. Then u have filter 0nfrom other measure. Been banging my head against a wall....

I think the solution lies in having a complete data points for 12months for each material I'd by date.

[Anonymous]

1. re you just trying to get the frequency total (P19 in the image) - YES

2. for a single/smaller selection of MATERIAL_IDs/dates? YES - ROLL12_COMPLETE_PDS_IND = Y

3. By default, you have your report filtered by ROLL12_COMPLETE_PDS_IND, so your values match the pre-calculated ones. -Reason is this is my Y period of 12 months. 

[Anonymous]

Many Thanks and let me test and come back, much appreocated as always for the positive feedback.