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

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"
      • 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