Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 dax.
Solved! Go to Solution.
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.
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.
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!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
23 | |
14 | |
11 | |
10 | |
7 |
User | Count |
---|---|
24 | |
15 | |
15 | |
14 | |
13 |