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
Icon for Community Champion rankCommunity Champion
6 years ago
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.