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

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

      • MFelix's avatar
        MFelix
        Super User
        5 years ago

        Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

        If the information is sensitive please share it trough private message.