Total Average 60 days , Not Work with Central , When connect relationship

[Chanleakna123]

hi All , 

I have the data below for the relationship connected to each other : my purpose is to calculate the formula of Total Average Back 60days , based on the Date Filter Selected . And also when i click on Central in Master Data , it will Average All data together , But right now i have got the wrong data , when i click on Central , the data are average together seems so small , which is no idea how to fix this . i have shared the link below , for the raw data and Master data of central , , Can you please do sample for me please ?

and this is my formula of Avg 60 days : 

**Rolling 60 = CALCULATE(AVERAGE('60 Days'[Value]),DATESINPERIOD('445 Calendar'[Date],max('60 Days'[Date]),-60,DAY))

**https://www.dropbox.com/scl/fi/d49semdn38oqn3gi16rw4/Testing.xlsx?dl=0&rlkey=h8e8apa98452pm93h3jtcbm...

[MFelix]

Hi @Chanleakna123 ,

In what way are you using the calendar table? are you using a filter? a Slicer? A table?

How are you giving the context date to your measure?

Try to redo the measure to:

Rolling 60 = CALCULATE(AVERAGE('Raw Data'[Value]),DATESINPERIOD('CALENDAR'[Date],max('Raw Data'[Date]),-60,DAY))

You may need to change the table names.

Check PBIX file attach.

Not sure if the model is correct based on your image, check it and please tell if it does not give correct result.

[Chanleakna123]

hi @MFelix  , really apprecaited on the sample , 

Can i Show the Sum Formula at the end of the Rolling 60 ? when i filter on Central and Date ? it should be 476.27 

thx

[MFelix]

Hi @Chanleakna123 ,

Measures are based on context so when you are calculating an average it always calculates average even for the totals.

However this can be overpassed by changing the measure a little bit, create a second measure with the following code:

Rolling 60 based on Customer = 
IF (
    HASONEVALUE ( 'Central Master Data'[Customer Name - Store[Customer]]] );
    'Raw Data'[Rolling 60];
    SUMX (
        SUMMARIZE (
            ALLSELECTED('Central Master Data'[Customer Name - Store[Customer]]]);
            'Central Master Data'[Customer Name - Store[Customer]]];
            "AVERAGE"; [Rolling 60]
        );
        [AVERAGE]
    )
)

If you want by material then use:

Rolling 60 based on Material = 
IF (
    HASONEFILTER(  'Raw Data'[Material Description]);
    [Rolling 60];
    SUMX (
        SUMMARIZE (
            ALLSELECTED('Raw Data'[Material Description host code];'Raw Data'[Material Description]);
            'Raw Data'[Material Description]; 'Raw Data'[Material Description host code];
            "AVERAGE"; [Rolling 60]
        );
        [AVERAGE]
    )
)

The two measure are diffenrent because they are based on context since they are part of different tables they have different calculations for the sum.

I left the previous measure on the table so you could see that the line values are the same.

Check PBIX file attach.

[Chanleakna123]

Hi @MFelix  , 

I am really so impressed with your formula , thanks you for quick response on this , 

now i would like to go further with Total Average 60 days again . since 476 also not correct on this , i am just discussing with my team is : Total Average 60 days by Customer Name : 171 or 204 or 100 are right , but the total 476  or 158 aren't right , it should be  Sum Total Value for 60 days = 57,152 / 60 days = 952.00 as the Total average of 60 days based on Central selected . can you help me on this sir ? 

[MFelix]

Hi @Chanleakna123 ,

Can you please tell me how you are getting the 57152? What are the filters for this sum?

I'm asking this because as refered measures are all based no context so you need to mak the correct filtering to get the result you need.

[Chanleakna123]

@MFelix  , hi , I have sum from 13 May 2020 back 60 days . It is 57152. 

[MFelix]

Hi @Chanleakna123 ,

On the datasource you have provided me between those dates there are no values. I only have from 13/03/2020 that is before the 60 days period before 13/05/2020.

Can you please check my file if I'm missing data.

[Chanleakna123]

Hi @MFelix  , oh really , sorry for that. Possible that u can do sample add another column , means if we have the other day appear , we remains sum for 60 days based on what we have on the the raw data . Then the total Average 60 days will be use with the value sum based on date filter and central filter. 
my purpose now : everything is setting up . Each customer name can see the right number , whereas only the Total result only. 

[MFelix]

Hi @Chanleakna123 ,

Check if this measure works for the customer.

Measure by customer = IF (
    HASONEVALUE ( 'Central Master Data'[Customer Name - Store[Customer]]] );
    [Rolling 60];
     CALCULATE(SUM('Raw Data'[Value]);FILTER(ALL('CALENDAR'[Date]);'CALENDAR'[Date]< MAX('Raw Data'[Date]) && 'CALENDAR'[Date]> MAX('Raw Data'[Date])-60 ))  / 60)

if it work I will explain and make it for product also.

[Chanleakna123]

hi @MFelix  , i have shared through dropbox , the data available from 16 Sep 2019 to 13 May 2020 , 

when i do calculation as you mentioned as the latest , by customers also turn wrong , and total also turn wrong ,Wherease Rolling 60 , by customer turn right , and total turn wrong only. 

When i select Date calendar 13 May , All are disappear . 

when i select Raw Date 13 May , the data appear to be wrong. 

it should be : when i filter on 13 May & Central & Customer Name : It will sum from 13 May to Last 60 Days with average of each , & Total turns to the Average when we see the Overall 952.30 

https://www.dropbox.com/scl/fi/d49semdn38oqn3gi16rw4/Testing.xlsx?dl=0&rlkey=h8e8apa98452pm93h3jtcbm...

https://www.dropbox.com/s/pf085zjlqkqdydx/RollingAverage.pbix?dl=0

[MFelix · Accepted solution]

Hi @Chanleakna123 ,

Sorry there was an incorrection when I transformed the date column on the Raw Data that was assuming dates of December.

The latest formula for customer is working properly now having the 952,53, however I have one question for material the value is the same so 952 also?

Please see attach file and sorry for the mistake.

[Chanleakna123]

Hi @MFelix  , i am so appreciated with your work and quick response over this even provide a sample calculation on this. this is so helpful to get my work done. 

All your Calculation is really fanstatic and work from my side.I'm happy 🙂 

[Chanleakna123]

hi @MFelix , now i would like to update my data , i gonna have another table Called daily Stock . also store history data 

Customer Code	Cus. Name	Code	Name	Effective Date	Daily Stock
1234567	MLP1	1234	Juice	3/13/2020	2,520
1234567	MLP1	3456	Milk	3/13/2020	1,820
345678	MLP2	1234	Juice	3/13/2020	432
345678	MLP2	3456	Milk	3/13/2020	2,520
857564	MLP3	1234	Juice	3/13/2020	1,820
857564	MLP3	3456	Milk	3/13/2020	432
978674	MLP4	1234	Juice	3/13/2020	780
978674	MLP4	3456	Milk	3/13/2020	402
120698	MLP5	1234	Juice	3/13/2020	650
120698	MLP5	3456	Milk	3/13/2020	285
574629	MLP6	1234	Juice	3/13/2020	2,520
574629	MLP6	3456	Milk	3/13/2020	1,820
120987	MLP7	1234	Juice	3/13/2020	432
120987	MLP7	3456	Milk	3/13/2020	288
1234567	MLP1	1234	Juice	3/12/2020	1,820
1234567	MLP1	3456	Milk	3/12/2020	432
345678	MLP2	1234	Juice	3/12/2020	288
345678	MLP2	3456	Milk	3/12/2020	1,080
857564	MLP3	1234	Juice	3/12/2020	1,430
857564	MLP3	3456	Milk	3/12/2020	1,820
978674	MLP4	1234	Juice	3/12/2020	432
978674	MLP4	3456	Milk	3/12/2020	288
120698	MLP5	1234	Juice	3/12/2020	780
120698	MLP5	3456	Milk	3/12/2020	1,820
574629	MLP6	1234	Juice	3/12/2020	432
574629	MLP6	3456	Milk	3/12/2020	1,820
120987	MLP7	1234	Juice	3/12/2020	432
120987	MLP7	3456	Milk	3/12/2020	288

my formula i am using : 

DOI.. = IFERROR([Total Daily Stock in BOS]/'Measure'[Measure by customer],0)

when i am using your file the Date need to be filtered 60 Days , Then when i connect relationship to be together my Daily Stock gonna sum together which is not making sense from my side.

Is it possible that the Date can only be filtered for only 1 based on the date selected and then will divide with Daily stock table based on what we selected on Date. 

i am thinking , Date is the the Problem. 

[MFelix]

Hi @Chanleakna123 ,

Not really sure if I understand you requirement, but again measure are based in context, so to what I can understand you want divide the  daily stock by the avegare for customer?

So picking up you table you would get the value for 12 of March for Juice 7.80 and divide that by the average of the last 60 days?

How are the tables related? what is the way you are calculating the measure [Total Daily Stock in BOS]?

Sorry once again but if you provide some expected result is easier to achieve the correct result.

[Chanleakna123]

Hi , 

**The daily stock on Juice on 03/13/2020 in Central 1:MLP1,2,3,4 = 5552 

**Average 60 days of Juice  on 03/13/2020 in Central1:MLP1,2,3,4 = 2333

therefore : DOI = 2.379 

And now i have connected with the Daily Stock , and the stock are sum up together while i may need only the stock Qty when i select on Date of 03/13/2020. 
The Audience may need to filter on 1 Date only.  i have attached below link for the connection. 

thanks you so much again for your help

https://www.dropbox.com/scl/fi/d49semdn38oqn3gi16rw4/Testing.xlsx?dl=0&rlkey=h8e8apa98452pm93h3jtcbm...

Thanks With REgards
Leakna 

[MFelix]

Hi @Chanleakna123 ,

Once again my values aren't matching with yours.

I have setup a Relationship between calendar and Stock Table and made a new table with products that makes the relationship between Stock Table and the Raw data.

This allows me to calculate the value correctly however If I look at the calculation you are presenting for Juice on 13/03 I have an average of 2.367,52 and you 2.333 I'm having a difference so the final value is also changed.

Before I send out my calculations can you please  tell me what's the base of the 2333.

[Chanleakna123]

hi , here is my 60 days avg , 2333.8 last 60 days 

[MFelix]

Hi @Chanleakna123

First of all let me apologize for the delay, and second because this solution implies changing the model and the previous measures, however this is getting the expected results (please check with additional values).

The steps:

Create New table for Materials:

Material  | Description host code Material description
1234       | Juice
3456       | Milk

Add the stock Table

Create the following relationships:

Materials[Material Description host code] (1) -> Raw Data[Material Description host code] (*)

Materials[Material Description host code] (1) -> Stock[Material Description host code] (*)

Central Master Data[Customer Name - Store[Customer] host code] (1) ->Raw Data[Customer Name - Store[Customer] host code] (*)

Central Master Data[Customer Name - Store[Customer] host code] (1) -> Stock[Customer Code] (*)

Calendar[Date] (1) -> Raw Data[Date] (*)

Calendar[Date] (1) -> Stock[Date] (*)

Redo your previous measure to:

Rolling 60 based on Material =
IF (
HASONEVALUE ( 'Raw Data'[Material Description host code] );
[Rolling 60];
CALCULATE (
SUM ( 'Raw Data'[Value] );
FILTER (
ALL ( 'CALENDAR'[Date] );
'CALENDAR'[Date] <= MAX ( 'CALENDAR'[Date] )
&& 'CALENDAR'[Date]
> MAX ( 'CALENDAR'[Date] ) - 60
)
) / 60
)


Rolling 60 based on Customer =
IF (
HASONEVALUE ( 'Central Master Data'[Customer Name - Store[Customer]]] );
[Rolling 60];
CALCULATE (
SUM ( 'Raw Data'[Value] );
FILTER (
ALL ( 'CALENDAR'[Date] );
'CALENDAR'[Date] <= MAX ( 'CALENDAR'[Date] )
&& 'CALENDAR'[Date]
> MAX ( 'CALENDAR'[Date] ) - 60
)
) / 60
)  

This changes are based on the new relationship with the calendar table.

Create the additional measure:

DOI = DIVIDE(SUM(Stock[Daily Stock]) ; [Rolling 60 based on Customer];0)

Be aware that if you select values where the raw data or stock doesn't have values you will get a blank value, this can be adjusted if needed but needs more variables in the measures.

Please check the file attach and tell me if eveything is working properly.

[Chanleakna123]

You’re so brilliant , thanks you so much for quick action 🙂