Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
prasy14
Helper I
Helper I

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

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)

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

Proud to be a Datanaut!

View solution in original post

5 REPLIES 5
Phil_Seamark
Employee
Employee

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!

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)

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

Proud to be a Datanaut!

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

Use your fiscal calendar instead of Calendar.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.