Forum Discussion

cmonteiro's avatar
cmonteiro
Regular Visitor
8 years ago
Solved

How to pull single value by filtering the dates

Hello,   I'm working with a table that contains cumulative values and I need to pull Today's Last Year value. The problem is that I cannot use SUM or COUNT.    Which formula can I nest with DATE(...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    cmonteiro,

    A bit change to Veles's DAX.

    Today Last year= CALCULATE ( 
		SUM ( Table[Value] ),
		FILTER ( 
			ALL ( Table[Date] ),
			Table[Date] = DATE(YEAR(NOW())-1,MONTH(NOW()),DAY(NOW())
		)
	)



    Regards,
    Lydia

Veles's avatar
Veles
Advocate V
8 years ago

This will work if you are using a slicer to filter on date rather than TODAY(). This assumes that the cumulative total starts from the first date in your data and doesn't reset at any point.

 

 

Day value = 
VAR CurrentDay = 
	MAX ( Table[Date] )
VAR PreviousDay = 
	CALCULATE ( 
		MAX ( Table[Date] ),
		FILTER ( 
			ALL ( Table[Date] ),
			Table[Date] < CurrentDay 
		)
	)
VAR PreviousDayValue = 
	CALCULATE ( 
		SUM ( Table[Value] ),
		FILTER ( 
			ALL ( Table ),
			Table[Date] = PreviousDay
		)
	)
RETURN
	SUM ( Table[Value] ) - PreviousDayValue

 

You can modify to use TODAY() instead but you would need to change the VAR CurrentDay line and the SUM ( Table[Value] ) on the final line to filter on TODAY() only.

 

 EDIT: Sorry misread the question.

 

You would just need to do:

 

PreviousDay = 
	CALCULATE ( 
		SUM ( Table[Value] ),
		FILTER ( 
			ALL ( Table[Date] ),
			Table[Date] = DATE(YEAR(NOW()-1,MONTH(NOW()),DAY(NOW())
		)
	)