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

cmonteiro's avatar
cmonteiro
Regular Visitor
8 years ago

Well, the cumulative sales today I will pull from another table. This one for the historicals I pull the Last Year values. 

 

Our data is accumulated separated in 2 tables. One have the numbers from 2008 until August 2017 and another have from September 2017 until today. It's not possible to merge the queries because the second table is being override. 

 

I wanted to separate cards to pull the inquiries for Today and Today last year.

 

I will try your codes. 

 

Thank you so much.

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