Value based on the first and last day of the month

[sonshine14916]

I have a Dispatch planner table which has the following columns:

I want create 2 measures as below:

1) Measure-1:  Get the OpeningStock based on the first day of the month as a list:

2) Measure-2: Get the ClosingStock based on the last day of the month as a list:

I tried using the Calculate measure and filters but I'm not getting it in the list format.

Thank you in advance. 

[talespin]

hi @sonshine14916 

In your visual, drop the Date column and this measure.

Opening Stock =

VAR _SelDate = SELECTEDVALUE('Table'[Date])

VAR _MthStartDate = EOMONTH(_SelDate, -1) + 1

RETURN IF(_SelDate = _MthStartDate, SELECTEDVALUE('Table'[OpeningStock]), BLANK())

Closing Stock =

VAR _SelDate = SELECTEDVALUE('Table'[Date])

VAR _MthStartDate = EOMONTH(_SelDate, 0)

RETURN IF(_SelDate = _MthStartDate, SELECTEDVALUE('Table'[ClosingStock]), BLANK())

[sonshine14916]

Hi @talespin , thanks for your inputs. It works as well...

Best regards,

GK

[talespin]

hi @sonshine14916 

You're welcome.

[Anonymous · Accepted solution]

Hi @sonshine14916 ,

The Table1 ‘s data is shown below:

Please follow these steps:
1. Use the following DAX expression to create a table named ‘First day of the month’

First day of the month = FILTER('Table1','Table1'[Date].[Day] = 1)

2. Use the following DAX expression to create a table named ‘Last day of the mont'

Last day of the month = FILTER(ADDCOLUMNS( VALUES(Table1),"Last day",EOMONTH('Table1'[Date],0)),[Date] = [Last day])

3. Final output

Best Regards,
Wenbin Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sonshine14916]

Hi Wenbin Zhou,

thank you very much for the solution. It works. 

Kind regards,

GK

[lbendlin]

Seems a bit redundant unless you have scenarios where the closing stock of the prior month doesn't match the opening stock of the new month?

Which measures have you tried?

[sonshine14916]

Hi @Ibendlin , you are correct, this may be redundant but I plan to use it for other purposes. Thanks for the feedback.

Best regards,

GK