Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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)

help2.PNG

          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])))
    Please help me correct my DAX for "FM KPI" 
    Thanks for help!
3 REPLIES 3
amitchandak
Super User
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

Hi  

            Thanks for your reply!

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

 
 

help4.PNG

 

help5.PNG

           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?

           Thanks again for your reply.

 

 

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])))

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

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.