Data Comparison between previous months

[Anonymous]

Hi,

I have below data

requirement is based on selection slicer on month(filter) , the measure should compare the equipment data with previous month equipment and gives total counts .

so for example - in Slicer May-20 is selected

1.  It will compare the data with Apr-20
2.  if May-20 equipment found in Apr-20 then "Data Exist"
3.  if May-20 equipment NOT found in Apr-20 then "New Entry"
4.  if Apr-20 requirement NOT found in May-20 then "Loss Entry"
5.  Sum of "Data Exist", Sum of "New Entry" and Sum of "Loss Entry"

Equipment	ProcessingDate
S1358	Apr-20
S3562	Apr-20
S3563	Apr-20
S3630	Apr-20
S3562	May-20
S3563	May-20
S3630	May-20
S8498	May-20
S8499	May-20
S3562	Jun-20
S3563	Jun-20
S8100	Jun-20

any suggestion will be appreciated. thanks.

[amitchandak]

@Anonymous , if you date, use date calendar and time intelligence. You can have measure like this can calculate you logic.

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

Use isblank() to check if the value is not there in a month like

isblank([last MTD Sales])

[Anonymous]

@amitchandak , Unit is String and SUM is giving error. 

mMTD UnitCount = CALCULATE(sum(Mtly_Data[UnitNumber]),DATESMTD(('Calendar'[Date])))

[Anonymous]

@amitchandak  and Experts,

I resolved above by changing SUM to count. Now with above two measure, I get count of current and previous month.

Ok now next steps is to have below logic..

1.  It will compare the data with Apr-20
2.  if May-20 equipment found in Apr-20 then "Data Exist"
3.  if May-20 equipment NOT found in Apr-20 then "New Entry"
4.  if Apr-20 requirement NOT found in May-20 then "Loss Entry"
5.  Sum of "Data Exist", Sum of "New Entry" and Sum of "Loss Entry"

[Anonymous · Accepted solution]

Hi @Anonymous ,

You can follow the below steps to achieve it. I just create a sample pbix file with all these steps, you can get it from this link.

1. Create a equipment dimension table

2. Create a measure as below to get the flag

Measure = 
VAR _t =
    CALCULATETABLE (
        VALUES ( 'Table'[Equipment] ),
        DATEADD ( 'Date'[Date], -1, MONTH ),
        ALL ( 'Table' )
    )
VAR _seledate =
    CALCULATE (
        MAX ( 'Table'[ProcessingDate] ),
        FILTER ( ALL ( 'Table' ), 'Table'[Equipment] = MAX ( 'Equipment'[Equipment] ) )
    )
RETURN
    IF (
        MAX ( Equipment[Equipment] ) IN VALUES ( 'Table'[Equipment] )
            && MAX ( Equipment[Equipment] ) IN _t,
        "Data Exist",
        IF (
            NOT MAX ( Equipment[Equipment] ) IN _t
                && MAX ( Equipment[Equipment] ) IN VALUES ( 'Table'[Equipment] ),
            "New Entry",
            IF ( _seledate < MAX ( 'Date'[Date] ), "Loss Entry" )
        )
    )

Best Regards

Rena