Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Subtract two unit quantities based on date slicer

I want to subtract the amount of units in inventory for different product types over a given timeframe. I've created the following dummy data to illustrate: The user should be able to select a...
Need Help
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    Add the following measure to your report:

     

    Inventory Variation =
VAR StartDate =
    MIN ( Inventory[Date] )
VAR EndDate =
    MAX ( Inventory[Date] )
RETURN
    CALCULATE (
        SUM ( Inventory[Inventory (Units)] );
        FILTER ( ALLSELECTED ( Inventory[Date] ); Inventory[Date] = EndDate )
    )
        - CALCULATE (
            SUM ( Inventory[Inventory (Units)] );
            FILTER ( ALLSELECTED ( Inventory[Date] ); Inventory[Date] = StartDate )
        )

    Final result is as follows:

    Regards,

    MFelix

hejszyszki's avatar
hejszyszki
Frequent Visitor
5 years ago

Hi guys, what about when new product appears? I am struggling to change formula to include also this aspect. Do you have any ideas? I think i understand why its not included in formula output ( cuz its not finding product name value to subtract) but no idea how to change to do so.
MFelix edhans any ideas?

Best Regards,
PP

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    HI hejszyszki ,

     

    Measures are based in context so if the product is added to the visualization the calculation shoud be done has expected however what probably is hapenning is since you only have the value on the first month the variation gives a blank value and does not retur any values in the visualzation. 

    • hejszyszki's avatar
      hejszyszki
      Frequent Visitor
      5 years ago

      Sounds reasonable MFelix , so my idea now to resolve this is to update the formula with something like IF blank then return "end month" value? Am i thinking correctly?
      IAR Comparison=
      VAR StartDate = MIN ( 'IAR Extract'[Version] )
      VAR EndDate = MAX ( 'IAR Extract'[Version] )
      RETURN CALCULATE ( SUM ( 'IAR Extract'[Financial Risk Value PUP] ),
      FILTER (ALLSELECTED('IAR Extract'[Version] ), 'IAR Extract'[Version] = EndDate )
      ) - CALCULATE (
      SUM ( 'IAR Extract'[Financial Risk Value PUP] ),
      FILTER ( ALLSELECTED ( 'IAR Extract'[Version] ), 'IAR Extract'[Version] = StartDate ) )

      And to refer, thats the formula ive been using...

      Best regards
      PP

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        HI hejszyszki 

         

        You just need to use coalesce something similar to:

        IAR Comparison =
VAR StartDate =
    MIN ( 'IAR Extract'[Version] )
VAR EndDate =
    MAX ( 'IAR Extract'[Version] )
RETURN
    COALESCE (
        CALCULATE (
            SUM ( 'IAR Extract'[Financial Risk Value PUP] ),
            FILTER (
                ALLSELECTED ( 'IAR Extract'[Version] ),
                'IAR Extract'[Version] = EndDate
            )
        )
            - CALCULATE (
                SUM ( 'IAR Extract'[Financial Risk Value PUP] ),
                FILTER (
                    ALLSELECTED ( 'IAR Extract'[Version] ),
                    'IAR Extract'[Version] = StartDate
                )
            ),
        CALCULATE (
            SUM ( 'IAR Extract'[Financial Risk Value PUP] ),
            FILTER (
                ALLSELECTED ( 'IAR Extract'[Version] ),
                'IAR Extract'[Version] = EndDate
            )
        )
    )