Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Divide Calculation - 6 month average wrong but why?

Hey Power BI Community! Please help me I am frustraded... I just want the DoAs Divided by parts for example Okctober 2019 = (5 DoAs / 222 Parts)*100 = 2,25 % These are my measures: Roll...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous 

    I think you want to calculate the percent by divide count of per month ID and sum of quantity rolling 6 month. I build three tables to have a test.

    Table1:

    Table2:

    Build a calendar table and build relationships between Table1 and CalendarTable 's Date column.

     

    calendar = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]))

     

     

    Build a measure to achieve your goal.

     

    Rolling Average 6 Months =
VAR Dos =
    CALCULATE (
        COUNT ( Table2[ID] ),
        FILTER (
            Table2,
            Table2[Year] = MAX ( 'calendar'[Year] )
                && Table2[Month] = MAX ( 'calendar'[Month] )
        )
    )
VAR Count_of_Parts =
    CALCULATE (
        SUM ( 'Table1'[Quantity] ),
        DATESINPERIOD ( 'calendar'[Date], FIRSTDATE ( 'calendar'[Date] ), 6, MONTH )
    )
RETURN
    DIVIDE ( Dos, Count_of_Parts )

     

    Result:

    You can download the pbix file from this link: Divide Calculation - 6 month average wrong but why

     

    Best Regards,

    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

AllisonKennedy's avatar
AllisonKennedy
Community Champion
6 years ago

Anonymous 

 

First, let's format your measure to read it more easily: 

 

Rolling Average 6 Months =
VAR result =
    CALCULATE (
        ( DIVIDE ( 'Parts per Month'[DoAs]; [Count of Parts] ) ) * 100 + 0;
        DATESINPERIOD ( Dates[Date]; MAX ( Dates[Date] ); -6; MONTH )
    )
RETURN
    IF (
        result;
        result;
        IF ( MAX ( Dates[Date2] ) > DATE ( 2019; 9; 30 ); 0; BLANK () )
    )

 

Here I don't understand why you have the first IF, so believe it can be simplified to the below, with no difference in result:

 

 

Rolling Average 6 Months =
VAR result =
    CALCULATE (
        ( DIVIDE ( 'Parts per Month'[DoAs]; [Count of Parts] ) ) * 100 + 0;
        DATESINPERIOD ( Dates[Date]; MAX ( Dates[Date] ); -6; MONTH )
    )
RETURN

result

 

You never make it to this part of the formula because you don't actually have a true false statement in your first IF statement:
    IF ( MAX ( Dates[Date2] ) > DATE ( 2019; 9; 30 ); 0; BLANK () )

 

For your question, I suspect this may be related to the fact that October is the first month, can you please also share what calculation you are using for [Count of Parts] and please tell us what is Dates[Date2] and why do you need to compare to 2019/9/30?