Forum Discussion

rbhattacharya's avatar
rbhattacharya
Icon for Helper I rankHelper I
5 years ago
Solved

Help for DAX For End Of Quarter

Hello,

 

Please refer to the attached screenshot which is a date filter in my report. Based on the current selected month (January), I need to derive some calculated value for the end of quarter month i.e. March in this case. I'm using ENDOFQUARTER Dax, but it is producing blank. This is the DAX I'm using:

 

TechnicalEmployeeHeadcountQt = CALCULATE(SUM('Budgeting v_IncomeStatement'[AdminCount]), DATESQTD(ENDOFQUARTER('Budgeting v_IncomeStatement'[PeriodFormatted].[Date])))
 
Maybe I'm not using the end of quarter DAX properly. Kindly help.
 
 
Regards,
RB
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi rbhattacharya ,

     

    According to my understanding, you want to get  the calculation of the last month of quarter based on the selected month in slicer.

     

    You need to create a new table for slicer like this:

     

    ForSlicer =
DISTINCT (
    SELECTCOLUMNS (
        CALENDAR ( MIN ( 'Original Table'[Date] ), MAX ( 'Original Table'[Date] ) ),
        "Year", YEAR ( [Date] ),
        "Month", FORMAT ( [Date], "MMMM" ),
        "MonthNo", MONTH ( [Date] ),
        "Quarter", QUARTER ( [Date] )
    )
)

     

    Then try the following formula:

     

    Measure =
VAR _lastMonthofQuarter =
    MAXX (
        FILTER (
            ALL ( 'ForSlicer' ),
            'ForSlicer'[Quarter] = MAX ( 'ForSlicer'[Quarter] )
                && 'ForSlicer'[Year] = MAX ( 'ForSlicer'[Year] )
        ),
        [MonthNo]
    )
RETURN
    CALCULATE (
        SUM ( 'Original Table'[Value] ),
        FILTER (
            'Original Table',
            'Original Table'[Date].[MonthNo] = _lastMonthofQuarter
                && 'Original Table'[Date].[Year] = MAX ( 'ForSlicer'[Year] )
        )
    )

     

    The final output is shown below:

     

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

3 Replies

  • selimovd's avatar
    selimovd
    Icon for Most Valuable Professional rankMost Valuable Professional
    5 years ago

    Hey rbhattacharya ,

     

    I didn't fully understand what you want to calculate. Maybe you want to explain that a little further.

    Try otherwise the following formula to get the calculation by the end of the quarter:

    TechnicalEmployeeHeadcountQt =
CALCULATE(
    SUM( 'Budgeting v_IncomeStatement'[AdminCount] ),
    DATESQTD( 'Budgeting v_IncomeStatement'[PeriodFormatted].[Date] ),
    ENDOFQUARTER( 'Budgeting v_IncomeStatement'[PeriodFormatted].[Date] )
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
     
    Best regards
    Denis
     

     

    • rbhattacharya's avatar
      rbhattacharya
      Icon for Helper I rankHelper I
      5 years ago

      Hello selimovd,

       

      Thanks a lot for the quick response. The suggested DAX didn't solve the problem. I'm getting null (0) value.

      I will try and explain the requirement once again. Based on the selected month in the date filter, I want to calculate some value for the end of the quarter month i.e. if the current selected month in the date filter is "January", then I want to calculate some value for "March" i.e. the end of the quarter month. Please note that the calculation should be just for the individual month (March) and not QTD. I want to retrieve value for March on selecting January. Hope this makes the requirement little more clear. 🙂

       

      Regards,

      RB

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi rbhattacharya ,

     

    According to my understanding, you want to get  the calculation of the last month of quarter based on the selected month in slicer.

     

    You need to create a new table for slicer like this:

     

    ForSlicer =
DISTINCT (
    SELECTCOLUMNS (
        CALENDAR ( MIN ( 'Original Table'[Date] ), MAX ( 'Original Table'[Date] ) ),
        "Year", YEAR ( [Date] ),
        "Month", FORMAT ( [Date], "MMMM" ),
        "MonthNo", MONTH ( [Date] ),
        "Quarter", QUARTER ( [Date] )
    )
)

     

    Then try the following formula:

     

    Measure =
VAR _lastMonthofQuarter =
    MAXX (
        FILTER (
            ALL ( 'ForSlicer' ),
            'ForSlicer'[Quarter] = MAX ( 'ForSlicer'[Quarter] )
                && 'ForSlicer'[Year] = MAX ( 'ForSlicer'[Year] )
        ),
        [MonthNo]
    )
RETURN
    CALCULATE (
        SUM ( 'Original Table'[Value] ),
        FILTER (
            'Original Table',
            'Original Table'[Date].[MonthNo] = _lastMonthofQuarter
                && 'Original Table'[Date].[Year] = MAX ( 'ForSlicer'[Year] )
        )
    )

     

    The final output is shown below:

     

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