Power BI DAX: Per month Count added ID's and count removed ID's

[Herasion]

I try to calculate:

Total SR-ID per month (easy)

Total SR-ID new (count ID's that previously were not available in all previous months)

Total SR-ID old (count ID's that were available in the last month, but not anymore in the current month)

For that I added an INDEX to the table that has the YearMonth I use:

Index = COUNTROWS( FILTER(VALUES(BridgePeriod[YearMonth]); BridgePeriod[YearMonth] < EARLIER(BridgePeriod[YearMonth]) ))

And I use the following DAX measures:

Measure 1 SR-ID New

COUNT SR-ID NEW =

VAR Ids = VALUES(CUSTAMOUNTREPORTTABLE[SR-ID])

VAR LastMonthIDs = CALCULATETABLE(VALUES(CUSTAMOUNTREPORTTABLE[SR-ID]);

FILTER( ALL(BridgePeriod); 'BridgePeriod'[Index] = SELECTEDVALUE('BridgePeriod'[Index]) -1))

Return CALCULATE( COUNTROWS( EXCEPT(Ids; LastMonthIDs)); CUSTAMOUNTREPORTTABLE[SR-ID] <> "")

Measure 2 SR-ID OLD

COUNT SR-ID OLD =

VAR Ids = VALUES(CUSTAMOUNTREPORTTABLE[SR-ID])

VAR LastMonthIDs = CALCULATETABLE(VALUES(CUSTAMOUNTREPORTTABLE[SR-ID]);

FILTER( ALL(BridgePeriod); 'BridgePeriod'[Index] = SELECTEDVALUE('BridgePeriod'[Index]) -1))

Return CALCULATE( COUNTROWS( EXCEPT(LastMonthIDs; Ids));CUSTAMOUNTREPORTTABLE[SR-ID] <> "")

Measure 3 SR-ID Total

COUNT SR-ID TOTAL =

CALCULATE( DISTINCTCOUNT(CUSTAMOUNTREPORTTABLE[Customer-ID]);

CUSTAMOUNTREPORTTABLE[Total]>0;

CUSTAMOUNTREPORTTABLE[Bedrijf]="001";

CUSTAMOUNTREPORTTABLE[SR-ID] <> "" )

With the index and above measures I expected to count per month added/removed ID's, but somehow it shows nothing close to what I expected. The dataset is included in the PowerBI file, link below.

Note that the CUSTAMOUNTREPORTTABLE has a lot of rows, way less then the SR-ID's. That's why I filter for CUSTAMOUNTREPORTTABLE[SR-ID] <> ""

I added the link to the Power BI file

https://1drv.ms/u/s!AliMmiR5bX0C3G0xfHLYruem4rQh?e=q1w98c

Any suggestions?

[amitchandak]

With these measures, you should be able to find with the group by SR ID

This month = calculate(countrows(CUSTAMOUNTREPORTTABLE),filter(BridgePeriod, BridgePeriod[Index] =max(BridgePeriod[Index])))
last month = calculate(countrows(CUSTAMOUNTREPORTTABLE),filter(BridgePeriod, BridgePeriod[Index] =max(BridgePeriod[Index])-1))
all month before this = calculate(countrows(CUSTAMOUNTREPORTTABLE),filter(BridgePeriod, BridgePeriod[Index] <max(BridgePeriod[Index])))

[Herasion]

Thanks for the fast reply!

I added your measures as well, but I still get strange results. See the image below, or the PBI file below:

Image:

https://imgur.com/a/1fm2PNC

PBI File:

https://1drv.ms/u/s!AliMmiR5bX0C3G0xfHLYruem4rQh?e=q1w98c

[v-easonf-msft]

Hi , @Herasion 

What is wrong with the result data  presented, can you show your expected results for testing？

And I make some modifications to measure as below  and  it show the same result.

COUNT SR-ID NEW =
VAR Ids =
    VALUES ( CUSTAMOUNTREPORTTABLE[SR-ID] )
VAR LastMonthIDs =
    CALCULATETABLE (
        VALUES ( CUSTAMOUNTREPORTTABLE[SR-ID] ),
        FILTER (
            ALL ( BridgePeriod ),
            'BridgePeriod'[Index]
                = SELECTEDVALUE ( 'BridgePeriod'[Index] ) - 1
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( EXCEPT ( Ids, LastMonthIDs ) ),
        CUSTAMOUNTREPORTTABLE[SR-ID] <> ""
    )

COUNT SR-ID OLD =
VAR Ids =
    VALUES ( CUSTAMOUNTREPORTTABLE[SR-ID] )
VAR LastMonthIDs =
    CALCULATETABLE (
        VALUES ( CUSTAMOUNTREPORTTABLE[SR-ID] ),
        FILTER (
            ALL ( BridgePeriod ),
            'BridgePeriod'[Index]
                = SELECTEDVALUE ( 'BridgePeriod'[Index] ) - 1
        )
    )
RETURN
    CALCULATE (
        COUNTROWS ( EXCEPT ( LastMonthIDs, Ids ) ),
        CUSTAMOUNTREPORTTABLE[SR-ID] <> ""
    )

Here is the demo.

pbix file

Best Regards,
Community Support Team _ Eason