cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## How To Calculate corresponding Fiscal Month(FM) KPI% by selected Fiscal Week( Filter)

Hi All,

When selecet Fiscal Week filter, how to caculate corresponding Fiscal Month(FM) KPI ? (please see attached image)

I had correct DAX for PreviousFM KPI ( all numbers match with DB):

PreviousFM KPI=
VAR prevmaxmonth = MAX(vw_Reporting[Month Slno])
VAR prevKPI= CALCULATE([KPI%],ALL(vw_Reporting[Fiscal Week]), vw_Reporting[Month Slno]=prevmaxmonth-1)
RETURN prevKPI
Month Slno --is month serial number, for example from fiscal year2020FM1to Fiscal year 2021 FM12, month serial number will be from 1,2.......12,13,...24)

But I cant get "FM KPI" correct, tried the following ways, but all caculated results  don't match with DB:

1. FM KPI =
VAR currentmonth = MAX(vw_Reporting[Month Slno])
VAR FM KPI= CALCULATE([SAT%], ALL(vw_ReportingData[Fiscal Week]),vw_Reporting[Month Slno]=currentmonth)
RETURN FM KPI
2. FM KPI= CALCULATE([SAT%], ALL(vw_Reporting[Fiscal Week]), FILTER(vw_Reporting, vw_Reporting[Month Slno]=Max(vw_Reporting[Month Slno])))
Thanks for help!
3 REPLIES 3
Super User

@Anonymous , Try like

FM KPI =
VAR currentmonth = MAXX(allselected(vw_Reporting),vw_Reporting[Month Slno])
VAR FM KPI= CALCULATE([SAT%], ALL(vw_ReportingData[Fiscal Week]),vw_Reporting[Month Slno]=currentmonth)
RETURN FM KPI
FM KPI= CALCULATE([SAT%], ALL(vw_Reporting[Fiscal Week]), FILTER(vw_Reporting, vw_Reporting[Month Slno]=currentmonth))

FM KPI =
VAR currentmonth = MAXX(allselected(vw_Reporting),vw_Reporting[Month Slno])
VAR FM KPI= CALCULATE([SAT%], ALL(vw_ReportingData[Fiscal Week]),vw_Reporting[Month Slno]=currentmonth)
RETURN FM KPI
FM KPI= CALCULATE([SAT%], ALL(vw_Reporting[Fiscal Week]), FILTER(vw_Reporting, vw_Reporting[Month Slno]=Max(vw_Reporting[Month Slno])))

Anonymous
Not applicable

I tried your DAX, it didn't work out. Please see enclosed images:

Have a question for you. In your DAX, you created a Variable "VAR FM KPI", but in your caculation(FM KPI= CALCULATE([SAT%], ALL(vw_Reporting[Fiscal Week]), FILTER(vw_Reporting, vw_Reporting[Month Slno]=Max(vw_Reporting[Month Slno]))) you didn't use this the variable which you created. Why?

Anonymous
Not applicable

Sorry, I typed wrong in "FM KPI" DAX, correct one should be:

1. FM KPI =
VAR currentmonth = MAX(vw_Reporting[Month Slno])
VAR FM KPI= CALCULATE([KPI%], ALL(vw_ReportingData[Fiscal Week]),vw_Reporting[Month Slno]=currentmonth)
RETURN FM KPI
2. FM KPI= CALCULATE([KPI%], ALL(vw_Reporting[Fiscal Week]), FILTER(vw_Reporting, vw_Reporting[Month Slno]=Max(vw_Reporting[Month Slno])))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors