Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cmonteiro
Regular Visitor

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!

 

 

1 ACCEPTED SOLUTION

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
Veles
Advocate V
Advocate V

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

 

Veles
Advocate V
Advocate V

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?

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

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?

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.

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

 

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.