Help for DAX For End Of Quarter

[rbhattacharya]

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

[v-eqin-msft · Accepted solution]

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.

[selimovd]

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

Blog: WhatTheFact.bi

Follow me: twitter.com/DenSelimovic

[rbhattacharya]

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