Calculate Daily average for a full period (November 2021) & Campaign on same month (same chart)

[haloboyscp]

Hey all,

So we want to show on a chart the daily average of acquisitions on November 2021 while at the same time for a particular period within the same month.

I came up with this formula, which seems working but I would need the same for November 2022 or other periods. How could I replicate easier or whats the best way to go

Average Subs = if('Subs 20 + Subs 21 + Subs 22'[Promotion]="Nov 21",calculate(COUNT('Subs 20 + Subs 21 + Subs 22'[Business Area]),filter(ALLEXCEPT('Subs 20 + Subs 21 + Subs 22','Subs 20 + Subs 21 + Subs 22'[Type],'Subs 20 + Subs 21 + Subs 22'[Channel]),or('Subs 20 + Subs 21 + Subs 22'[Promotion]="Nov 21",'Subs 20 + Subs 21 + Subs 22'[Promotion]="2021 Black Friday")))/CALCULATE(DISTINCTCOUNT('Subs 20 + Subs 21 + Subs 22'[Data Subs]),filter(ALLEXCEPT('Subs 20 + Subs 21 + Subs 22','Subs 20 + Subs 21 + Subs 22'[Channel]),or('Subs 20 + Subs 21 + Subs 22'[Promotion]="Nov 21",'Subs 20 + Subs 21 + Subs 22'[Promotion]="2021 Black Friday"))),calculate(COUNT('Subs 20 + Subs 21 + Subs 22'[Business Area]),ALLEXCEPT('Subs 20 + Subs 21 + Subs 22','Subs 20 + Subs 21 + Subs 22'[Type],'Subs 20 + Subs 21 + Subs 22'[Canal],'Calendar'[Promotion]))/CALCULATE(DISTINCTCOUNT('Subs 20 + Subs 21 + Subs 22'[Data Subs]),ALLEXCEPT('Subs 20 + Subs 21 + Subs 22','Subs 20 + Subs 21 + Subs 22'[Channel],'Calendar'[Promotion])))

[lbendlin]

Please visit daxformatter.com for better DAX code readability

Average Subs =
IF(
    'Subs 20 + Subs 21 + Subs 22'[Promotion] = "Nov 21",
    CALCULATE(
        COUNT( 'Subs 20 + Subs 21 + Subs 22'[Business Area] ),
        FILTER(
            ALLEXCEPT(
                'Subs 20 + Subs 21 + Subs 22',
                'Subs 20 + Subs 21 + Subs 22'[Type],
                'Subs 20 + Subs 21 + Subs 22'[Channel]
            ),
            OR(
                'Subs 20 + Subs 21 + Subs 22'[Promotion] = "Nov 21",
                'Subs 20 + Subs 21 + Subs 22'[Promotion] = "2021 Black Friday"
            )
        )
    )
        / CALCULATE(
            DISTINCTCOUNT( 'Subs 20 + Subs 21 + Subs 22'[Data Subs] ),
            FILTER(
                ALLEXCEPT(
                    'Subs 20 + Subs 21 + Subs 22',
                    'Subs 20 + Subs 21 + Subs 22'[Channel]
                ),
                OR(
                    'Subs 20 + Subs 21 + Subs 22'[Promotion] = "Nov 21",
                    'Subs 20 + Subs 21 + Subs 22'[Promotion] = "2021 Black Friday"
                )
            )
        ),
    CALCULATE(
        COUNT( 'Subs 20 + Subs 21 + Subs 22'[Business Area] ),
        ALLEXCEPT(
            'Subs 20 + Subs 21 + Subs 22',
            'Subs 20 + Subs 21 + Subs 22'[Type],
            'Subs 20 + Subs 21 + Subs 22'[Canal],
            'Calendar'[Promotion]
        )
    )
        / CALCULATE(
            DISTINCTCOUNT( 'Subs 20 + Subs 21 + Subs 22'[Data Subs] ),
            ALLEXCEPT(
                'Subs 20 + Subs 21 + Subs 22',
                'Subs 20 + Subs 21 + Subs 22'[Channel],
                'Calendar'[Promotion]
            )
        )
)

Please provide sample data that covers your issue or question completely.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...


https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523