Calculate Sum with Date Filters for Fiscal Year

[ColePBI]

Hello PBI Elite!

   I need a bit of help.  First time poster, but long time lerker (3 months now).  I am still learning DAX, but this problem i just cannot wrap my head around even though im sure someone will know the answer right away.  I have also watched numerous videos and read a lot of posts to no avail.  To get on with it...

I have a Calendar table with a relationship to a Fact table that has Lease Expiration dates, Signal dates, and Size Values (3, 4, 5, etc.).

What i am trying to do is Filter by FY for table, graphs, etc., but summarize all of the Size Values within 2 conditional statements.  

Here is my code that, of course, works for FY21, but is not dynamic for the other years:

##########################################

Total Worked =

    CALCULATE (

        SUM('CompleteTracker'[TShirtSizeValue]),

            FILTER(

            'CompleteTracker',

            'CompleteTracker'[LeaseExpire] >= DATE (2020,7,1) &&

            'CompleteTracker'[SignalDate] <= DATE (2021,6,30)

))

##########################################

As you can imagine, i would want FY21 = 142, FY22 = a new sum, FY23, etc.  

What i believe i am running into is that the Date filter (FY) is trumping Calculate to only look at values within that FY.  Should i be using variables to hold the date for the calculation?   

Any help would be greatly appreciated!  

[Anonymous]

// What you have to do is this.
// T is your CompleteTracker. Please bear in mind
// that if you put any filters on any attributes
// of Calendar (like, say, filtering by weekends)
// these filters will not be honored by the formula.
// the formula is only concerned with the start
// and end date seen in the calendar in the current
// context. If you have a need to honor other filters,
// then you need to state HOW you want them to work
// since you are using 2 different date fields in
// the fact table and it's not clear how to filter
// by other attributes in the Calendar table.

[Total Worked] =
var __firstDate = MIN( 'Calendar'[Date] )
var __lastDate = MAX( 'Calendar'[Date] )
return
	CALCULATE(
		SUM( T[TShirtSizeValue] ),
		CALCULATETABLE(
			VALUES( T[LeaseExpire] ),
			T[LeaseExpire] >= __firstDate,
			ALL( 'Calendar' )
		),
		CALCULATETABLE(
			VALUES( T[SignalDate] ),
			T[SignalDate] <= __lastDate,
			ALL( 'Calendar' )
		)
	)

Best

D

[ColePBI]

Thank you both for the replies. @Anonymous @az38 

@Anonymous I think that is very close, but i am getting the dreaded "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."  Not entirely sure how to handle that in this context...

To give additional context on the relationship (my fault for not providing it earlier) is:

Thus, my understanding is, when I add FiscalYear to the table, it then chooses the Calendar Date to filter the SizingMaster table which then only has certain LeaseExpire and SignalDate in that Date range.  Your solution has me build additional ALL tables that are outside of that initial filter, find the TShirtSize value with the conditional filters and then summarize those and return them.  

'Calendar'[FiscalYear] -> 'Calendar'[Date] ----> 'SizingMaster'[LeaseExpire] -> 'SizingMaster' [SignalDate] and/or 'SizingMaster'[TShirtSize]

*Also, the Calendar table i am using is from Avi and I have used FY numerous other times, so i am convinced it is a DAX filtering issue, but i could be wrong!  

** Again, really appreciate all of the assistance!

[Anonymous · Accepted solution]

I can't see where the formula would give you the error you mention... This is a variation on the subject. Try it. It shouldn't give you any errors.

[Total Worked] =
var __firstDate = MIN( 'Calendar'[Date] )
var __lastDate = MAX( 'Calendar'[Date] )
var __leaseFilter = 
	CALCULATETABLE(
		VALUES( T[LeaseExpire] ),
		T[LeaseExpire] >= __firstDate,
		ALL( 'Calendar' )
	)	
var __signalFilter =
	CALCULATETABLE(
		VALUES( T[SignalDate] ),
		T[SignalDate] <= __lastDate,
		ALL( 'Calendar' )
	)
return
	CALCULATE(
		SUM( T[TShirtSizeValue] ),
		__leaseFilter,
		__signalFilter,
		ALL( 'Calendar' )
	)

You can also try the same thing but expressed differently:

[Total Worked] =
var __firstDate = MIN( 'Calendar'[Date] )
var __lastDate = MAX( 'Calendar'[Date] )
var __leaseFilter = 
	FILTER(
		ALL( T[LeaseExpire] ),
		T[LeaseExpire] >= __firstDate
	)	
var __signalFilter =
	FILTER(
		ALL( T[SignalDate] ),
		T[SignalDate] <= __lastDate
	)
return
	CALCULATE(
		SUM( T[TShirtSizeValue] ),
		__leaseFilter,
		__signalFilter,
		ALL( 'Calendar' )
	)

Best

D

[ColePBI]

@Anonymous Just wanted to say, really appreciate the help!  You nailed it.  

[Anonymous]

If your calendar table is correctly connected to the fact table, then slicing by FY should already give you the correct numbers. No stunts in code necessary. Please bear in mind that if your calendar joins on a column in your fact table, this column should be hidden in the fact table. You should never slice by it if you want to return correct and predictable results.

Best
D

[az38]

Hi @ColePBI 

It depends on your data model.

how does FY connected with 'CompleteTracker'?

Anyeay, also try to use ALL() in filter

Total Worked =
    CALCULATE (
        SUM('CompleteTracker'[TShirtSizeValue]),
            FILTER(
            ALL('CompleteTracker'),
            'CompleteTracker'[LeaseExpire] >= DATE (2020,7,1) &&
            'CompleteTracker'[SignalDate] <= DATE (2021,6,30)
))

[ColePBI]

Hi @az38, thanks for the reply.

So the Date table connectes 'Calendar'[date]->'CompleteTracker'[LeaseExpiration].  The Calendar table is providing the FY.  I have used it in several other areas, so i think we are good to go there.  

Also, you can see from the code, i am trying the ALL(), which i also believed would do the trick, but doesnt.  

To summarize, how do i replace "DATE (2020,7,1)" "DATE(2021,6,30)" with the FY filter from the Calendar (Date) Table?  

I believe this has something to do with Dates overriding explicit filters within CALCULATE....

[az38]

@ColePBI 

you can try smth like

Total Worked =
var _LeaseExpireFY = RELATED('CalendarTable'[FY])
var _curSignalDate = MAX('CompleteTracker'[SignalDate])
var _SignalDateFY = CALCULATE(MAX('CalendarTable'[FY]), 'CalendarTable'[Date] = _curSignalDate )

RETURN
    CALCULATE (
        SUM('CompleteTracker'[TShirtSizeValue]),
            FILTER(
            ALL('CompleteTracker'),
            'CompleteTracker'[LeaseExpire] >= _LeaseExpireFY  &&
            'CompleteTracker'[SignalDate] <= _SignalDateFY 
))