Dax help Monthly annualised ℅

[Anonymous]

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 Dec16 onwards. But i want 2 calculate attrition % for 1st 11 months which has diff formula like
Avg of monthly % (for Jan avg of Jan, for Feb Avg of Jan & Feb so on) * 12 month
can anybody suggest changes 1st DAX measure to calculate same.
also the hard coded date will not work if any process start in latest year e.g. 2018/2019. I need fix for this as well.
Help appreciated. Thanks in advance.

[v-lid-msft]

Hi @Anonymous ,

According to your describe, I mock some data and create this measure.

Measure = 
VAR d =
    SELECTEDVALUE ( Data[Date] )
RETURN
    CALCULATE (
        SUM ( Data[Attrition] ) / MONTH ( d ),
        FILTER (
            ALL ( Data ),
            AND ( YEAR ( Data[Date] ) = YEAR ( d ), MONTH ( Data[Date] ) <= MONTH ( d ) )
        )
    ) * 12

If it doesn't meet your requirement, kindly share your excepted result based on my sample data to me.

BTW, pbix as attached.

Best regards,

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

[Anonymous]

Hi can somebody suggest what best we can do in this case.

[v-lid-msft]

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]

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]

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.

[v-lid-msft · Accepted solution]

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.

[Anonymous]

Hi, Thaks a lot for looking into the problem. Help appreciated.

Have attached both excel and pbix file. please note below requirement.

1-1st 11 month has different formula. It’s a average monthly % multiply by 12 months

2-as soon as 12 month trend is available will use different formula i.e. sum of attritions for last 12 months divided by average billable no for last 12 months

3-everytime we select month in slicer it should show previous 12 month % in a graph. it will show previous 12 months data (first month is decided by first month with available Billable month. Whichever month has Billable nos in each SOW will be considered as first Billable month).

4-we have to also include forecasted attritions in graph highlighted with different colour or we can wrap those 2 columns with different color. It will be always last 2months.

5-then will have different types of analysis like male/Female, Location wise , Band/Designation wise, SOW wise, Group wise, comparison of current year previous year, Comparison of different processes, Quarterly etc

6- Refer excel file columns in Green Color Columns AL to AU. first 11 months has different formula and as soon as 12 month trend is available we have changed the formula.

7- IF your refer PBIX file i have alrady created 12MonthRolling Attr% measure. please edit this measure put DAX formula to calculate Attrition % for first 11 months (refer excel file formula Green Color Columns AL to AU) This DAX formula also should  work for SOW E and SOW F as they start in mid year (not necessary it will start on Jan; will decide it by availability of Billable No).

8- So the hard coded month in 12MonthRolling Attr% measure should dynamically change from Jan-YY to Nov-YY

so only 2 issues Attrition % for first 11 months and hard coded date in measure should be change dynamically for first 11 months and after 11 months as soon 12 month trend is available.

PBIX and Excel File

Thanks