Forum Discussion

Sri57's avatar
Sri57
Frequent Visitor
2 years ago
Solved

DAX Query

I have created a dax query to get count of names, considering both the current fiscal year and potentially the previous fiscal year if no data exists for the current year.please find the following da...
@Dax
@help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi  Sri57 ,

     

    Try to modify your formula like below:

     

    Adequate_FYTD_Audit_Session = 
VAR CurrentDate = MAX('Date'[Fiscal Year ID])
VAR SelectedSession = SELECTEDVALUE('Date'[Session])

VAR result = 
    CALCULATE(
        COUNT('Audit'[ENGAGEMENT_NAME]),
        'Audit'[RATING] = "Adequate" && 
        'Audit'[Session] = SelectedSession && 
        'Audit'[FISCAL_YEAR_ID] = CurrentDate
    )

VAR max_notblank = 
    CALCULATE(
        MAX('Audit'[FISCAL_YEAR_ID]),
        FILTER(
            ALL('Audit'), 
            'Audit'[FISCAL_YEAR_ID] < CurrentDate && 
            'Audit'[Session] = SelectedSession && 
            NOT ISBLANK('Audit'[FISCAL_YEAR_ID])
        )
    )

VAR result1 = 
    CALCULATE(
        COUNT('Audit'[ENGAGEMENT_NAME]),
        'Audit'[RATING] = "Adequate" && 
        'Audit'[Session] = SelectedSession && 
        'Audit'[FISCAL_YEAR_ID] = max_notblank
    )

RETURN
    IF(ISBLANK(result), result1, result)

     

    Best Regards,
    Adamk Kong

     

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

Sahir_Maharaj's avatar
Sahir_Maharaj
Icon for Super User rankSuper User
2 years ago

Hello Sri57,

 

Can you please try the following:

 

Adequate_FYTD_Audit

Adequate_FYTD_Audit =
VAR CurrentDate = MAX('Date'[Fiscal Year ID])
VAR result =
    CALCULATE(
        COUNT('Audit'[ENGAGEMENT_NAME]),
        FILTER(
            ALLEXCEPT('Audit', 'Audit'[ENGAGEMENT_NAME]),
            'Audit'[RATING] = "Adequate" &&
            'Audit'[FISCAL_YEAR_ID] = CurrentDate
        ),
        CROSSFILTER('Date'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None)
    )
VAR max_notblank =
    CALCULATE(
        MAX('Audit'[FISCAL_YEAR_ID]),
        FILTER(
            ALLSELECTED('Audit'),
            'Audit'[FISCAL_YEAR_ID] < CurrentDate &&
            NOT(ISBLANK('Audit'[FISCAL_YEAR_ID]))
        ),
        CROSSFILTER('Date'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None)
    )
VAR result1 =
    CALCULATE(
        COUNT('Audit'[ENGAGEMENT_NAME]),
        FILTER(
            ALLEXCEPT('Audit', 'Audit'[ENGAGEMENT_NAME]),
            'Audit'[RATING] = "Adequate" &&
            'Audit'[FISCAL_YEAR_ID] = max_notblank
        ),
        CROSSFILTER('Date'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None)
    )
RETURN
    IF(ISBLANK(result), result1, result)

 

Adequate_FYTD_Audit_Session

Adequate_FYTD_Audit_Session =
VAR CurrentDate = MAX('Date'[Fiscal Year ID])
VAR SelectedSession = SELECTEDVALUE('Date'[Session])
VAR result =
    CALCULATE(
        COUNT('Audit'[ENGAGEMENT_NAME]),
        FILTER(
            ALLEXCEPT('Audit', 'Audit'[ENGAGEMENT_NAME]),
            'Audit'[RATING] = "Adequate" &&
            'Audit'[Session] = SelectedSession &&
            'Audit'[FISCAL_YEAR_ID] = CurrentDate
        ),
        CROSSFILTER('Date'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None)
    )
VAR max_notblank =
    CALCULATE(
        MAX('Audit'[FISCAL_YEAR_ID]),
        FILTER(
            ALLSELECTED('Audit'),
            'Audit'[FISCAL_YEAR_ID] < CurrentDate &&
            'Audit'[Session] = SelectedSession &&
            NOT(ISBLANK('Audit'[FISCAL_YEAR_ID]))
        ),
        CROSSFILTER('Date'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None)
    )
VAR result1 =
    CALCULATE(
        COUNT('Audit'[ENGAGEMENT_NAME]),
        FILTER(
            ALLEXCEPT('Audit', 'Audit'[ENGAGEMENT_NAME]),
            'Audit'[RATING] = "Adequate" &&
            'Audit'[Session] = SelectedSession &&
            'Audit'[FISCAL_YEAR_ID] = max_notblank
        ),
        CROSSFILTER('Date'[Date], 'Audit'[ENGAGEMENT_PLANNED_START], None)
    )
RETURN
    IF(ISBLANK(result), result1, result)

Hope this helps!