Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi,
I am pretty new to DAX and Power BI. I am trying to calculate the number of Item batches in stock per day for a table like below:
InventoryTransaction | ItemNumber | Item Batch | StatusDate | Quantity |
5637151470 | 10001332 | 0000000007 | 09 February 2021 | -56 |
5637151472 | 10001332 | 0000000007 | 09 February 2021 | -56 |
5637151471 | 10001332 | 0000000007 | 09 February 2021 | 56 |
5637151473 | 10001332 | 0000000007 | 09 February 2021 | 56 |
5637151582 | 10001332 | 0000000008 | 11 February 2021 | 56 |
5637151583 | 10001332 | 0000000009 | 11 February 2021 | 56 |
5637150642 | 10001332 | 0000000007 | 16 March 2021 | 56 |
5637151351 | 10001332 | 0000000007 | 1 May 2021 | -56 |
5637151352 | 10001332 | 0000000008 | 1 May 2021 | -56 |
5637151353 | 10001332 | 0000000009 | 1 May 2021 | -56 |
on 09/02/2021 should be 0 as the qty sum for that day is zero (and the same for all periods till 11/02/21)
on 11/02/2021 should be 2 (and the same for all periods till 16/02/21)
on 16/03/2021 should be 3 (the same for all periods till 01/05/2021)
on 01/05/2021 should be 0
I have tried below but is not good enough:
Count Distint Batches =
VAR _currdatekey = MAX ('dwh view_DateRelative'[DayDate])
RETURN
CALCULATE(
DISTINCTCOUNT ('dwh view_fact_InventoryTransaction'[ItemBatchKey]),
FILTER(
ALLSELECTED ('dwh view_DateRelative'[DayDate]),
ISONORAFTER ('dwh view_DateRelative'[DayDate],_currdatekey, DESC)
))
Can you help please?
Regards
Thank you AmitChandak,
I have tried several things but I cannot get it to work properly, due to my limited knowledge of DAX.
I would really appreciate if could you be more specific, please?
User | Count |
---|---|
25 | |
12 | |
8 | |
6 | |
6 |
User | Count |
---|---|
26 | |
12 | |
11 | |
8 | |
7 |