topic Re: Value based on the first and last day of the month in DAX Commands and Tips

Value based on the first and last day of the month

sonshine14916 — Thu, 29 Feb 2024 17:51:37 GMT

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.

Re: Value based on the first and last day of the month

lbendlin — Fri, 01 Mar 2024 01:11:00 GMT

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?

Re: Value based on the first and last day of the month

Anonymous — Fri, 01 Mar 2024 05:22:24 GMT

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.

Re: Value based on the first and last day of the month

talespin — Fri, 01 Mar 2024 09:06:28 GMT

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())

Re: Value based on the first and last day of the month

sonshine14916 — Fri, 01 Mar 2024 16:15:24 GMT

Hi Wenbin Zhou,

thank you very much for the solution. It works.

Kind regards,

Re: Value based on the first and last day of the month

sonshine14916 — Fri, 01 Mar 2024 16:16:45 GMT

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

Best regards,

Re: Value based on the first and last day of the month

sonshine14916 — Fri, 01 Mar 2024 16:19:24 GMT

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

Best regards,

Re: Value based on the first and last day of the month

talespin — Sat, 02 Mar 2024 05:11:02 GMT

hi sonshine14916

You're welcome.