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(YEAR(NOW()-1,MONTH(NOW()),DAY(NOW()), to return a single value filtered by the specific date?

 

Thank you in advance for any help!

 

 

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

8 Replies

  • Veles's avatar
    Veles
    Advocate V
    8 years ago

    So you have a table like this?

     

    Date        Value
01/01/18  500
02/01/18  1000
03/01/18  1500

    E.g. here the value for each day that you want would be 500?

     

    Couple of questions

     

    Do you have any gaps in the data (e.g. skipped days with no data)

    Does the cummulative value reset at any point, e.g. at the start of a new year?

    • 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?

  • 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())
		)
	)