Forum Discussion

Anonymous

Not applicable

7 years ago

Solved

Dax help Monthly annualised ℅

12Month Rolloing Attr%=CALCULATE([12 month Attrition Nos]/[12 month Avg Billable No], FILTER('Calendar', 'Calendar'[Date]>= date(2016,12,1))) I hv data frm jan16 onwards, above formula works fine frm...

Need Help

v-lid-msft

7 years ago

Hi Anonymous ,

Sorry for not understand your requirement clearly, now I have fixed my formula, We can use the following measure to archive your requirement:

Test =
VAR selectedtime =
    MAX ( 'Calendar'[Date] )
VAR starttime =
    CALCULATE (
        MIN ( 'BillableNos'[Month] ),
        FILTER (
            ALL ( BillableNos ),
            AND (
                AND (
                    'BillableNos'[Billable Nos] > 0,
                    NOT ISBLANK ( BillableNos[Billable Nos] )
                ),
                BillableNos[Sow Name] IN FILTERS ( 'SOWMaster'[SOW] )
            )
        )
    )
VAR starttimemonth =
    DATE ( YEAR ( starttime ), MONTH ( starttime ), 1 )
VAR t =
    ADDCOLUMNS (
        ADDCOLUMNS (
            DISTINCT ( ALLSELECTED ( BillableNos[Month] ) ),
            "MonthBill", CALCULATE (
                SUM ( BillableNos[Billable Nos] ),
                FILTER ( ALLSELECTED ( 'BillableNos' ), [Month] = EARLIER ( [Month] ) )
            ) + 0
        ),
        "MonthNo", CALCULATE (
            COUNT ( ActualAttritionRaw[Attrition Gross/Net] ),
            FILTER (
                ALLSELECTED ( ActualAttritionRaw ),
                AND (
                    ActualAttritionRaw[Attrition Gross/Net] = "Net",
                    AND (
                        MONTH ( ActualAttritionRaw[Last Working Day] ) = MONTH ( [Month] ),
                        YEAR ( ActualAttritionRaw[Last Working Day] ) = YEAR ( [Month] )
                    )
                )
            )
        ) + 0
    )
VAR t1 =
    ADDCOLUMNS (
        t,
        "MonthAtt", IF ( [Month] < starttimemonth, BLANK (), DIVIDE ( [MonthNo], [MonthBill], 0 ) )
    )
RETURN
    IF (
        selectedtime < starttimemonth,
        BLANK (),
        IF (
            selectedtime < ( starttimemonth + 365 ),
            FORMAT (
                AVERAGEX ( FILTER ( t1, [Month] <= selectedtime ), [MonthAtt] ) * 12,
                "Percent"
            ),
            FORMAT (
                DIVIDE (
                    SUMX (
                        FILTER ( t1, AND ( [Month] <= selectedtime, [Month] >= selectedtime - 365 ) ),
                        [MonthNo]
                    ),
                    AVERAGEX (
                        FILTER ( t1, AND ( [Month] <= selectedtime, [Month] >= selectedtime - 365 ) ),
                        [MonthBill]
                    ),
                    0
                ),
                "Percent"
            )
        )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lid-msft

Community Support

7 years ago

Hi Anonymous ,

I am sorry for respone so late, we can use the following to meet your requirement.

Test =
VAR selectedtime =
    MAX ( 'Calendar'[Date] )
VAR totalnos =
    CALCULATE (
        COUNT ( ActualAttritionRaw[Attrition Gross/Net] ),
        FILTER (
            ALLSELECTED ( ActualAttritionRaw ),
            AND (
                ActualAttritionRaw[Attrition Gross/Net] = "Net",
                ActualAttritionRaw[Last Working Day] <= selectedtime
            )
        )
    ) + 0
VAR totalbill =
    CALCULATE (
        SUM ( BillableNos[Billable Nos] ),
        FILTER ( ALLSELECTED ( BillableNos ), BillableNos[Month] <= selectedtime )
    ) + 0
VAR starttime =
    MINX (
        FILTER (
            ALL ( ActualAttritionRaw ),
            ActualAttritionRaw[SOW] IN FILTERS ( SOWMaster[SOW] )
        ),
        [Last Working Day]
    )
RETURN
    IF (
        selectedtime < starttime,
        BLANK(),
        IF (
            selectedtime < ( starttime + 365 ),
            FORMAT ( DIVIDE ( totalnos, totalbill, 0 ) * 12, "Percent" ),
            FORMAT (
                CALCULATE (
                    [12 month Attrition Nos] / [12 month Avg Billable No],
                    FILTER ( 'Calendar', 'Calendar'[Date] >= starttime + 365 )
                ),
                "Percent"
            )
        )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous

Not applicable

7 years ago

Hi Dong Li, Thank you so much for looking into this. i am not able to open the link seems it has been expired. But no worry i have used formula from msg body.

To be honest i did not understand much about your complicated formula but It works fine with SOW E. Pretty similar results for both of our formulae but it fails on SOW F. Our requirement is for 1st 11 months formula is Avg of Monthly Attrition % * 12 ( avg(Jan18)*12, avg(Jan18, Feb18)*12, avg(Jan18,Feb18, Mar18)*12, until Nov18 ) and assoon as 12 month trend data is available; from 12 month Dec18 onwards formula is Sum of 12 Attrions / Avg of Last 12 month Billable No. refer snapshots below. 1-Results of SOW E . 2-Actual Requirement calculation SOW F

Also wanted to know why Excel and Power BI percentages are slightly differes from each other? they dont give same results.

Regards

Amol

v-lid-msft

Community Support

7 years ago

Hi Anonymous ,

Sorry for not understand your requirement clearly, now I have fixed my formula, We can use the following measure to archive your requirement:

Test =
VAR selectedtime =
    MAX ( 'Calendar'[Date] )
VAR starttime =
    CALCULATE (
        MIN ( 'BillableNos'[Month] ),
        FILTER (
            ALL ( BillableNos ),
            AND (
                AND (
                    'BillableNos'[Billable Nos] > 0,
                    NOT ISBLANK ( BillableNos[Billable Nos] )
                ),
                BillableNos[Sow Name] IN FILTERS ( 'SOWMaster'[SOW] )
            )
        )
    )
VAR starttimemonth =
    DATE ( YEAR ( starttime ), MONTH ( starttime ), 1 )
VAR t =
    ADDCOLUMNS (
        ADDCOLUMNS (
            DISTINCT ( ALLSELECTED ( BillableNos[Month] ) ),
            "MonthBill", CALCULATE (
                SUM ( BillableNos[Billable Nos] ),
                FILTER ( ALLSELECTED ( 'BillableNos' ), [Month] = EARLIER ( [Month] ) )
            ) + 0
        ),
        "MonthNo", CALCULATE (
            COUNT ( ActualAttritionRaw[Attrition Gross/Net] ),
            FILTER (
                ALLSELECTED ( ActualAttritionRaw ),
                AND (
                    ActualAttritionRaw[Attrition Gross/Net] = "Net",
                    AND (
                        MONTH ( ActualAttritionRaw[Last Working Day] ) = MONTH ( [Month] ),
                        YEAR ( ActualAttritionRaw[Last Working Day] ) = YEAR ( [Month] )
                    )
                )
            )
        ) + 0
    )
VAR t1 =
    ADDCOLUMNS (
        t,
        "MonthAtt", IF ( [Month] < starttimemonth, BLANK (), DIVIDE ( [MonthNo], [MonthBill], 0 ) )
    )
RETURN
    IF (
        selectedtime < starttimemonth,
        BLANK (),
        IF (
            selectedtime < ( starttimemonth + 365 ),
            FORMAT (
                AVERAGEX ( FILTER ( t1, [Month] <= selectedtime ), [MonthAtt] ) * 12,
                "Percent"
            ),
            FORMAT (
                DIVIDE (
                    SUMX (
                        FILTER ( t1, AND ( [Month] <= selectedtime, [Month] >= selectedtime - 365 ) ),
                        [MonthNo]
                    ),
                    AVERAGEX (
                        FILTER ( t1, AND ( [Month] <= selectedtime, [Month] >= selectedtime - 365 ) ),
                        [MonthBill]
                    ),
                    0
                ),
                "Percent"
            )
        )
    )

BTW, pbix as attached.

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-lid-msft
Community Support
7 years ago
Hi Anonymous ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Dax help Monthly annualised ℅

Related content

Monthly Automatic Change

Calculating Annualised Rate of Return

Monthly Count not working

Average monthly sales buy aggregated

Monthly Burn Rate