Forum Discussion

Cocohcy's avatar
Cocohcy
Icon for Microsoft Employee rankMicrosoft Employee
3 years ago
Solved

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

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    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 )

5 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      Cocohcy
      Icon for Microsoft Employee rankMicrosoft Employee
      3 years ago

      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's avatar
        Jihwan_Kim
        Icon for Super User rankSuper User
        3 years ago

        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 )
  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    3 years ago

    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's avatar
      Cocohcy
      Icon for Microsoft Employee rankMicrosoft Employee
      3 years ago

      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.