Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I have an Inventory table which will have Inventory on hand column. What i am looking for is to get the beginning on hand value for period - week,month,quarter (Current Year and Previous Year)
Inventory table Columns ohhanddate onhandvalue
02/08/2017 1100
02/13/2017 2000
02/21/2017 1000
02/17/2017 3000
03/02/2017 2500
03/08/2017 3000
03/12/2017 2000
.
.
04/01/2017 3300
By Month
February Begining on hand= 1100
March Begining on hand= 2500
April Begining on hand= 3300
By Quater
QTR 1 Begining on hand = 1100
I have dimDate table , which is related to Inventory table withonhanddate and i did the below DAX which is not working as expected and I want to show only beginning value from Feb not Jan as my inventory table has data from feb.
BeginValue = CALCULATE(inventory[onhandvalue],FIRSTDATE(inventory[ohhanddate]),calendar[IsInCurrentYear]=1)
Solved! Go to Solution.
Bare in mind that LOOKUPVALUE will error if it detects multiple values during the lookup. Your small sample dataset has none, but if you had multiple entries for the first day of a month you will get an errorl
Here is a lazy way to resolve that 🙂
Measure =
VAR FirstDateForPeriod = FIRSTDATE('Table1'[onhanddate])
RETURN CALCULATE(SUM('Table1'[onhandvalue]),'Table1'[onhanddate] = FirstDateForPeriod)
HI @prasy14
I think this calculated measure might be close
Measure = VAR FirstDateForPeriod = FIRSTDATE('Table1'[onhanddate]) RETURN LOOKUPVALUE('Table1'[onhandvalue],Table1[onhanddate],FirstDateForPeriod)
FirstDate = Var Firstdatefact = FirstDate('Table1'[onhanddate]) Return LOOKUPVALUE('Table1'[onhandValue], 'Fiscal Calendar'[CalendarDate], Firstdatefact)
Bare in mind that LOOKUPVALUE will error if it detects multiple values during the lookup. Your small sample dataset has none, but if you had multiple entries for the first day of a month you will get an errorl
Here is a lazy way to resolve that 🙂
Measure =
VAR FirstDateForPeriod = FIRSTDATE('Table1'[onhanddate])
RETURN CALCULATE(SUM('Table1'[onhandvalue]),'Table1'[onhanddate] = FirstDateForPeriod)
This is getting me correct values but I atill get data from January instead of Feb
Use your fiscal calendar instead of Calendar.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
145 | |
108 | |
107 | |
89 | |
61 |