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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Cocohcy
Microsoft Employee
Microsoft Employee

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

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

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

Cocohcy_0-1673519208802.png

 

1 ACCEPTED SOLUTION

Hi,

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

 

Jihwan_Kim_0-1673863403617.png

 

 

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 )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

5 REPLIES 5
v-zhangti
Community Support
Community Support

Hi, @Cocohcy 

 

You can try the following methods.

Month = MONTH([Date])
Quarter = QUARTER([Date])

vzhangti_0-1673595715013.png

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

vzhangti_1-1673595804460.png

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.

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. 

Screenshot 2023-01-16 170523.pngTarget.png

Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1673520617157.png

 

 

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 )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

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?Target.png

Hi,

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

 

Jihwan_Kim_0-1673863403617.png

 

 

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 )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.