Forum Discussion

ae19bu's avatar
ae19bu
Frequent Visitor
6 years ago
Solved

DAX Measure to sum values differently based on description

I need help creating a QTD measure that will allow me to sum values differently based on the value description.  I have Table A with contains various values that are daily sums or End of Month snapshots.  The following Measure works only for daily sums but not End of Month Snapshots:
    CALCULATE (
      [Amount],  // Sum(TableA[Value])
      REMOVEFILTERS ( 'Date' ),
      'Date'[Year Month Number] <= LastMonthAvailable,
      'Date'[Year Quarter Number] = LastYearQuarterAvailable
     )

 

DateDescriptionValue Desired Outcome if 8/31 is selected
7/1/2020Income5 Income is aggregated by Day
7/2/2020Income6  
7/3/2020Income7  
….    
8/31/2020Income5  
1/31/2020# Accounts100 # Accounts contains latest month snapshot
2/28/2020# Accounts120  
3/31/2020# Accounts130  
…    
8/31/2020# Accounts150  

Here is my attempt to create a measure that will sum daily and end of month snapshot appropriately, but it does not work. Please help.
Amount QTD =
VAR LastMonthAvailable =MAX ( 'Date'[Year Month Number] )
VAR LastYearQuarterAvailable =MAX ( 'Date'[Year Quarter Number] )
VAR Result =
SUMX(
   TableA,
   SWITCH(
    TRUE(),
    TableA [Description] IN {"# Accounts"},
    CALCULATE (
       [Amount], // Sum(TableA[Value])
       REMOVEFILTERS ( 'Date' ),
       'Date'[Year Month Number] = LastMonthAvailable,
       'Date'[Year Quarter Number] = LastYearQuarterAvailable
     ) ,
    NOT Table A [Description] IN {"# Accounts"},
    CALCULATE (
      [Amount],  // Sum(TableA[Value])
      REMOVEFILTERS ( 'Date' ),
      'Date'[Year Month Number] <= LastMonthAvailable,
      'Date'[Year Quarter Number] = LastYearQuarterAvailable
     )
  )
)
RETURN
Result
DAX
Measures
QTD
Switch()

1 Reply