Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ae19bu
Frequent Visitor

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
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@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.

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors