12 Month Moving Average on Calculated Field

[jdonovan]

Hey All - I'm trying o write DAX to solve for a 12 month moing average on a calculated percentage.  The percentage I'm interesting in averaging is a Win Rate (Amount Won / Amount Closed).

Amount Won and Amount Closed are both calculated fields off of the Amount field which is a native measure to the dataset I'm pulling from Salesforce.

The two possible date fields I can use to limit to 12 months are (1) the Master Close Date which is the close date of the sale or (2) a simple date field from a Date Table I imported using DAX code to grab fiscal years, etc.  Not sure if those two are interchangeable since they are joined.

Thanks in advance!

[Parkavi]

@jdonovanPlease try using a quick measure "Rolling Average" for the base measure "Win rate".

You can use date field from the date table instead from your fact.

[jdonovan]

thanks @Parkavi , so that seems so work when displayed with the Date hierarchy from the DateTable, but when using Fiscal Date applications included within the date field, it appears to fail and calculate on blanks.  Any idea why that may be?

Left chart & table are using fiscal year/quarter along with month from the date hierarchy.  Right is using year/quarter/month from date hierarchy.  Note: year and quarter won't line up between fiscal and calendar.