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

Hello,

 

1) Yes, our historical data is static. Have been already summarized. It shows values on the 1st and 15th of each month. 

 

2) It starts in October, so the amount increases until September and then starts again from zero. 

 

The problem is that our current data is dynamic so I can easily work with DAX formulas, but the historicals I have to select a specif date in order to compare to a previous period. 

 

Thank you

Veles's avatar
Veles
Advocate V
8 years ago

I think my edited code might be what you are after. Are you just trying to pull the cumulative sales for today and then this time last year?

  • 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