Divide Calculation - 6 month average wrong but why?

[Anonymous]

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:

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())) 


Why does Power BI calculate in Octuber rate of 14,41 % ? How can I change this? 
Thank you for your time.

[Anonymous]

Hi @Anonymous 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

[Anonymous · Accepted solution]

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. 

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Thank you for your time! My target is to show data from october 2019 - to today/future

Date2 = Dates[Date]

Dates = CALENDAR(DATE(2010;01;01);DATE(2025;12;31))

DoAs = var result =count('Stammdaten Komplett'[ID])

return if(result; result; IF(MAX(Dates[Date2])>DATE(2019;9;30);0; BLANK()))

Count of Parts = var result = SUM('All Parts'[adjusted quantity])

return if(result; result; IF(MAX(Dates[Date2])>DATE(2019;9;30);0; BLANK()))

adjusted quantity = if('All Parts'[Quantity]>10; 1; 'All Parts'[Quantity])

I am sorry but more data is not possible without leaking sensitiv data

[AllisonKennedy]

@Anonymous
Thanks, that data helps a bit. Your current measure is not doing any filter on the date. You can either use a visual or page filter or date slicer:
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range#:~:text=You%20can%20use%20the%20relative%20date%20slicer%20just%20like%20any,corner%20of%20the%20slicer%20visual.

Or if you want to do this in a measure, you could try:
Count of Parts =
SUMX( FILTER('All Parts', 'All Parts'[Month] > DATE(2019;9;30)), 'All Parts'[adjusted quantity])

Depending on what other requirements you have, may need to add some additional tweaks to add/ignore other filters.

Also, I don't understand why you need Date2? You should be able to just use Dates[Date] whenever that is needed.

[Anonymous]

Thank you for your answer!

Rolling Average 6 Months = var result = calculate((DIVIDE('Parts per Month'[DoAs]; [Count of Parts]))+0; DATESINPERIOD(Dates[Date]; MAX(Dates[Date]); -6; month))

return if(result; result; IF(MAX(Dates[Date2])>DATE(2019;9;30);0; BLANK()))

I already have a filter on the date. 

[AllisonKennedy]

You could also try using DATESINPERIOD with CALCULATE:

MEASURE = CALCULATE( SUM('All Parts'[adjusted quantity]), DATESBETWEEN(Dates[Date], DATE(2019;9;30), TODAY()))

https://docs.microsoft.com/en-us/dax/datesinperiod-function-dax

[AllisonKennedy]

@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?