topic Re: First day of month in a date in DAX Commands and Tips

First day of month in a date

Anonymous — Thu, 28 Apr 2022 09:12:43 GMT

Hello everyone,

How to find an earlierst date in a date column but groupped by year and month? Is there any simple formula for this?
Example below:

* Left table is a final one. On a right side you can find a source.

My goal is to create a matrix so I think, first step would be to create a "Y axis" measure and then "Values" measure taking into account date must be the same as Y axis ?

Would be grateful for help 🙂

Re: First day of month in a date

amitchandak — Thu, 28 Apr 2022 10:36:38 GMT

Anonymous ,

Calculate(firstnonblankvalue('Date'[Date], [Measure]), filter(allselected('Date'), 'Date'[Month Year] = max( 'Date'[Month Year]) ) )

Re: First day of month in a date

Anonymous — Thu, 28 Apr 2022 11:21:19 GMT

Hello! Thank you for quick respond. What "[Measure]" means exactly ?

Re: First day of month in a date

v-chenwuz-msft — Tue, 03 May 2022 08:35:23 GMT

Hi Anonymous ,

Maybe you can try this method.

1 create a measure.

Measure =

if(CALCULATE(MIN('Table'[Date]), FILTER(ALL('Table'),[Year Month]=SELECTEDVALUE('Table'[Year Month]))) =SELECTEDVALUE('Table'[Date]),1)

2 put this measure in fiilter pane and set it show items is 1.

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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