cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
prasy14 Regular Visitor
Regular Visitor

DAX for Beginning of Month Value

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)

1 ACCEPTED SOLUTION

Accepted Solutions
Phil_Seamark Super Contributor
Super Contributor

Re: DAX for Beginning of Month Value

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 Smiley Happy

 

Measure = 
VAR FirstDateForPeriod = FIRSTDATE('Table1'[onhanddate])
RETURN CALCULATE(SUM('Table1'[onhandvalue]),'Table1'[onhanddate] = FirstDateForPeriod)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

5 REPLIES 5
Phil_Seamark Super Contributor
Super Contributor

Re: DAX for Beginning of Month Value

HI @prasy14

 

I think this calculated measure might be close

 

Measure = 
	VAR FirstDateForPeriod = FIRSTDATE('Table1'[onhanddate])
	RETURN LOOKUPVALUE('Table1'[onhandvalue],Table1[onhanddate],FirstDateForPeriod)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Phil_Seamark Super Contributor
Super Contributor

Re: DAX for Beginning of Month Value

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 Smiley Happy

 

Measure = 
VAR FirstDateForPeriod = FIRSTDATE('Table1'[onhanddate])
RETURN CALCULATE(SUM('Table1'[onhandvalue]),'Table1'[onhanddate] = FirstDateForPeriod)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

sgsukumaran Member
Member

Re: DAX for Beginning of Month Value

FirstDate = Var Firstdatefact = FirstDate('Table1'[onhanddate]) Return LOOKUPVALUE('Table1'[onhandValue], 'Fiscal Calendar'[CalendarDate], Firstdatefact)

prasy14 Regular Visitor
Regular Visitor

Re: DAX for Beginning of Month Value

This is getting me correct values but I atill get data from January instead of Feb

Highlighted
sgsukumaran Member
Member

Re: DAX for Beginning of Month Value

Use your fiscal calendar instead of Calendar.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 281 members 2,981 guests
Please welcome our newest community members: