Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Data Comparison between previous months

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"

 

 

EquipmentProcessingDate
S1358Apr-20
S3562Apr-20
S3563Apr-20
S3630Apr-20
S3562May-20
S3563May-20
S3630May-20
S8498May-20
S8499May-20
S3562Jun-20
S3563Jun-20
S8100Jun-20

 

 

any suggestion will be appreciated. thanks.

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    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

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak , Unit is String and SUM is giving error. 

       

       

       

       

      mMTD UnitCount = CALCULATE(sum(Mtly_Data[UnitNumber]),DATESMTD(('Calendar'[Date])))
       
       
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        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"