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

Anonymous's avatar
Anonymous
Not applicable
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

cmonteiro's avatar
cmonteiro
Regular Visitor
8 years ago

Dear Lydia,

 

Thank you for your reply. If we are using a Dimension Date Table, the "Table[Date]" field is from the same table where the values are or from the Dimension Date table?

 

Thank you,

Carla

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    cmonteiro,

    You can use Dimension date table, in this case, you would need to create slicer using year field of dimension date table to filter the measure.

    Regards,
    Lydia