Total supply by subgroep connected with date table

[Ronald123]

Hello,

I have problem's to connected my total suply measure with my date table.

I need to calculate the total supply of items filter by subgroup's

The change of the total supply must be in the month when the articel is purchased or saled.

See the Pbix file, thanks.

Beste Regards,

Ronald
https://drive.google.com/file/d/0B1cNUVQxpE63cE9Wa056TXRLVzg/view?usp=sharing

[v-huizhn-msft]

Hi @Ronald123,

Please use the formula below.

Total suply2 = 
CALCULATE (
    SUM ( 'Stock'[Amount] ),
    FILTER (ALL('Date'),
       'Date'[Date]<=MAX('Date'[Date]))
	)
+CALCULATE (
    SUM ( 'Stock'[Sale] ),
  FILTER (ALL('Date'),
       'Date'[Date]<=MAX('Date'[Date]))     
)

I test it in the .pbix file, there is blank row, so there is this a blank month row as the screenshot.



I have no your resouce table excel file, so can't edit the Power Query. Please right click->Edit Query->Remove blank rows as shown in highlighted in yellow background, and check if it works fine.



Best Regards,
Angelia 

[Ronald123]

@v-huizhn-msft, Angelia, Thank you for you're reaction.

The blank row isseu is solved, de datetable was loaded until 30-12-2017, the new pbix file i have attached in this message.

The isseu is there still, see at mont number 5. Until May there must be a total suplly of 4.  Item 24, of subgroup fruit has a saled date of 2 June 2017. The measure gives the total of 3, and that is wrong. Can you take a look on the "This is what i need table" in de the pbix file?

Thanks,

Ronald

https://drive.google.com/open?id=0B1cNUVQxpE63ZXkxdXZJbnRWZkU 

[v-huizhn-msft]

Hi @Ronald123,

The attachment link is invalid. Please update it.

Thanks,
Angelia

[Ronald123]

@v-huizhn-msft,

https://drive.google.com/open?id=0B1cNUVQxpE63VjJGTzY2TlFBaWs

[v-huizhn-msft]

Hi @Ronald123,

After research, I finnaly get the expectedresult, but I still need to find out how to calculate, I post it here for your reference, then we can discuss.

Create measure using the formula.

Measure = 
VAR currentDate =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
         SUM ( 'Stock'[Amount] ),
        FILTER (
            Stock,
            ( Stock[Date purchase] <= currentDate
                && Stock[sale-date] >= currentDate )
        )
    )

Then create another measure using the formula below. I wonder how to get right result without sale value.

Measure 2 = CALCULATE(Stock[Measure],FILTER(ALL('Date'),'Date'[Date]<=MAX('Date'[Date])))

I still research it and post the update, thanks for understanding.

Best Regards,
Angelia

[Ronald123]

@v-huizhn-msft

Hello Angelia,

The measure works fine. Are you still resarch the measure or can i accept this solution?

Beste Regards,

Ronald

[v-huizhn-msft · Accepted solution]

Hi @Ronald123,

You can mark it as answer first, becasue it gets the right result. I will research it when I free. Thanks for understanding.

Best Regards,
Angelia

[Ronald123]

@v-huizhn-msft Hello Angelia,

Thank you for researching my question.

I have the follow problem when i created the measure.

[sale-date] is not a colum in de pbix file, when i try the colum [date sale], this give me the wrong totals.

Is it possible to share the pbix file?

Beste Regards,

Ronald

[v-huizhn-msft]

Hi @Ronald123,

Please review and download the attachment.