DAX Measure to sum values differently based on description

[ae19bu]

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
     )

Date	Description	Value		Desired Outcome if 8/31 is selected
7/1/2020	Income	5		Income is aggregated by Day
7/2/2020	Income	6
7/3/2020	Income	7
….
8/31/2020	Income	5
1/31/2020	# Accounts	100		# Accounts contains latest month snapshot
2/28/2020	# Accounts	120
3/31/2020	# Accounts	130
…
8/31/2020	# Accounts	150

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

[amitchandak · Accepted solution]

@ae19bu , You have closingbalanceQuarter, that can date the last day balance of the closingbalanceQuarter. Also, you have the last nonblankvalue

closingbalanceQuarter(Sum('Table'[Value]), Date[Date])

Please date calendar with that QTD Total

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last Day QTD total

Now with lastnonblankvalue, I using table date not the date from date table to make sure if balance is not there on last date it take last avaiable date

QTD Sales = CALCULATE(lastnonblankvalue(Sales[Date],SUM(Sales[Sales Amount])),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(lastnonblankvalue(Sales[Date],SUM(Sales[Sales Amount])),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

I have video for this  - https://youtu.be/yPQ9UV37LOU

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.