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 f...
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

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"
Anonymous's avatar
Anonymous
Not applicable
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