How to write DAX to select time First Month End in Each Quarter

[Cocohcy]

Try to figure out the data of First Month End in Each Quarter

eg, how to figure out the value of 1/28/2021

[v-zhangti]

Hi, @Cocohcy 

You can try the following methods.

Month = MONTH([Date])

Quarter = QUARTER([Date])

Measure = 
VAR _firstmonth = CALCULATE ( MIN ( 'Table'[Month] ),
        FILTER ( ALL ( 'Table' ), [Quarter] = SELECTEDVALUE ( 'Table'[Quarter] ) )
    )
RETURN
    CALCULATE ( MAX ( 'Table'[Date] ),
        FILTER ( ALL ( 'Table' ),
            [Month] = _firstmonth
            && [Quarter] = SELECTEDVALUE ( 'Table'[Quarter] )
        )
    )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

[Cocohcy]

Thanks for the reply. The method looks great and simple to understand. But my data range is rolling between 2021 to 2023 so it comes out 2023 data when calcuate 2021. Please see my result in Picture 1. 

Additionally, to be more specific on what I am asking. Actually I am trying to find next qtr's firstmonth last snapshot to compare with all the data in this qtr. See picture 2. 

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your data model.

Expected result measure: =
VAR _quarterlast =
    ENDOFQUARTER ( Data[SnapShotDate] )
VAR _monthlast =
    ENDOFMONTH ( Data[SnapShotDate] )
VAR _newtable =
    MINX (
        ADDCOLUMNS (
            FILTER ( ALL ( Data ), ENDOFQUARTER ( Data[SnapShotDate] ) = _quarterlast ),
            "@monthlast", CALCULATE ( ENDOFMONTH ( Data[SnapShotDate] ) )
        ),
        [@monthlast]
    )
RETURN
    IF ( HASONEVALUE ( Data[SnapShotDate] ), _newtable )

[Cocohcy]

Hi Jihwan, the way works in my PBI calculation. But it;s complicate for me to modify, may I know if I want to catch next qtr's first month last snapshot to compare with this qtr's data. How to acheive in DAX?

[Jihwan_Kim · Accepted solution]

Hi,

Please kindly check the below picture and the attached pbix file whether it suits your requirement.

Target Date To: =
VAR _quarterlast =
    ENDOFQUARTER ( Data[SnapShotDate] )
VAR _newtable =
    MINX (
        ADDCOLUMNS (
            FILTER ( ALL ( Data ), ENDOFQUARTER ( Data[SnapShotDate] ) > _quarterlast ),
            "@monthlast", CALCULATE ( ENDOFMONTH ( Data[SnapShotDate] ) )
        ),
        [@monthlast]
    )
RETURN
    IF ( HASONEVALUE ( Data[SnapShotDate] ), _newtable )