Measure is creating duplicate rows in a table

[keckraguilar]

I am trying to sum charges for the two previous months. The grand total is right but the grand total is duplicating down every row. Below are the two DAX formulas I am using. I think the issue is with the "ALL" being in the formula but I can't figure it out. I also included a screenshot of the fields in my table. 

Total Charges Current Month =

  VAR __MaxMonth = MAXX( ALL( 'Volume Files' ), 'Volume Files'[Month Number] )

  VAR __Result = CALCULATE(SUM('Volume Files'[Total Charges]), FILTER( ALL( 'Volume Files' ), [Month Number] = __MaxMonth), FILTER( ALL( 'Volume Files' ), 'Volume Files'[Discharge Date - Fiscal Year]="FY2024" ))

RETURN

  __Result

Total Charges Previous Month =

  VAR __MaxMonth = MAXX( All( 'Volume Files' ), 'Volume Files'[Month Number] )

  VAR __Result = CALCULATE(SUM('Volume Files'[Total Charges]), FILTER( ALL( 'Volume Files' ), [Month Number] = __MaxMonth - 1 ), FILTER( All( 'Volume Files' ), 'Volume Files'[Discharge Date - Fiscal Year]="FY2024" ))

RETURN

  __Result

[v-kaiyue-msft · Accepted solution]

Hi @keckraguilar ,

You can change your expression to something like this.

Total Charges Current Month = 
VAR __CurrentPatient =
    SELECTEDVALUE ( 'Volume Files'[Patient Encounter - Service Line 2] )
VAR __MaxMonth =
    MAXX ( ALL ( 'Volume Files' ), 'Volume Files'[Month Number] )
VAR __Result =
    CALCULATE (
        SUM ( 'Volume Files'[Total Charges] ),
        FILTER (
            ALL ( 'Volume Files' ),
            [Month Number] = __MaxMonth
                && 'Volume Files'[Discharge Date - Fiscal Year] = "FY2024"
                && 'Volume Files'[Patient Encounter - Service Line 2] = __CurrentPatient
        )
    )
VAR __total =
    CALCULATE (
        SUM ( 'Volume Files'[Total Charges] ),
        FILTER (
            ALLSELECTED ( 'Volume Files' ),
            [Month Number] = __MaxMonth
                && 'Volume Files'[Discharge Date - Fiscal Year] = "FY2024"
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Volume Files'[Patient Encounter - Service Line 2] ),
        __Result,
        __total
    )

Total Charges Previous Month = 
VAR __CurrentPatient =
    SELECTEDVALUE ( 'Volume Files'[Patient Encounter - Service Line 2] )
VAR __MaxMonth =
    MAXX ( ALL ( 'Volume Files' ), 'Volume Files'[Month Number] )
VAR __Result =
    CALCULATE (
        SUM ( 'Volume Files'[Total Charges] ),
        FILTER (
            ALL ( 'Volume Files' ),
            [Month Number] = __MaxMonth - 1
                && 'Volume Files'[Discharge Date - Fiscal Year] = "FY2024"
                && 'Volume Files'[Patient Encounter - Service Line 2] = __CurrentPatient
        )
    )
VAR __total =
    CALCULATE (
        SUM ( 'Volume Files'[Total Charges] ),
        FILTER (
            ALLSELECTED ( 'Volume Files' ),
            [Month Number] = __MaxMonth - 1
                && 'Volume Files'[Discharge Date - Fiscal Year] = "FY2024"
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Volume Files'[Patient Encounter - Service Line 2] ),
        __Result,
        __total
    )

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[audreygerred]

You can create one measure for your charges as Total Charges = SUM( 'Volume Files'[Total Charges] ), then create a second measure for prior month like Total Charges Previous Month = CALCULATE ([Total Charges], PREVIOUSMONTH ('DateTable', [Date]). Then, when you filter to the year and month your [Total Charges] measure will show that amount and [Total Charges Previous Month] will return the prior month. 

PREVIOUSMONTH function (DAX) - DAX | Microsoft Learn

[keckraguilar]

I am trying to make the formula dynamic and not have to manually change the date field every month when data refreshes