Forum Discussion

SBIM's avatar
SBIM
Icon for Helper I rankHelper I
3 years ago
Solved

MTD Measure Showing Prior Month Results

I have a measure that uses a fiscal year and month to sum the most recent month like this. 

 

FMTDSales = CALCULATE(
    SUM(JobsWithMilestones[GrossAmount]),
    JobsWithMilestones[ContractfiscalYearMonth] = MAX(JobsWithMilestones[ContractfiscalYearMonth])
)
 
It works perfectly except at the beginning of each month any market that does not yet have a contract entered still shows a sum of the prior month. So my total is fine, but the individual market numbes are way off since it is showing their prior month's data.
 

 

I know it is showing the max contract year and month for those markets, but is this expected behavior? Is there a way to make it show nothing for those markets that have no contracts yet so I they match the total?

Need Help
  • jdbuchanan71's avatar
    jdbuchanan71
    3 years ago

    SBIM 

    Try it like this where we get the max MonthYear across all markets and use that.

    FMTDSales =
VAR _MonthYear =
    CALCULATE (
        MAX ( JobsWithMilestones[ContractfiscalYearMonth] ),
        ALL ( JobsWithMilestones[Market] )
    )
RETURN
    CALCULATE (
        SUM ( JobsWithMilestones[GrossAmount] ),
        JobsWithMilestones[ContractfiscalYearMonth] = _MonthYear
    )

2 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    3 years ago

    SBIM 

    Try it like this where we get the max MonthYear across all markets and use that.

    FMTDSales =
VAR _MonthYear =
    CALCULATE (
        MAX ( JobsWithMilestones[ContractfiscalYearMonth] ),
        ALL ( JobsWithMilestones[Market] )
    )
RETURN
    CALCULATE (
        SUM ( JobsWithMilestones[GrossAmount] ),
        JobsWithMilestones[ContractfiscalYearMonth] = _MonthYear
    )
    • SBIM's avatar
      SBIM
      Icon for Helper I rankHelper I
      3 years ago

      That did the trick thanks for the quick response.