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

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
            )
        )
    )

     

    • hejszyszki's avatar
      hejszyszki
      Frequent Visitor
      5 years ago

      Unfortunetly it is outputting 0 when new product appears, also 0 when it dissapear. My goal is to make a comparison of changes, so lets say inventory in January of X product is 10 and in February is 0 - then graph goes -10 and when Y product in January is 0 and in Feb 10 graph output +10 ---> The if statement does not work because for new apperance it outputs a value X and for dissapearance it outputs X (instead of -X)...

      • hejszyszki's avatar
        hejszyszki
        Frequent Visitor
        5 years ago

        Guys, MFelix, any ideas for that? Or should i close the topic🤔? 

        Best regards