cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
p0werb1
Frequent Visitor

All days on month SUM up to certain date

Hi everyone,

 

I am trying to create a measure that should SUM a value up to a certain date. But getting error when using my "certain date" as i variable in my filter context.

 

 

Failed to resolve name 'maxDate'. It is not a valid table, variable, or function name.

 

 

 My goal is to display all dates in a certain month and use my maxDate variable to hide/stop sum my measure up to that certain date. The DAX-query looks like this right now:

 

 

DEFINE
    VAR maxDate =
        DATEVALUE ( "2020-03-04" )
    
    VAR calendarFilterTable =
    	FILTER (
        	MyCalendarTable,
            MyCalendarTable[Date] >= DATEVALUE ( "2020-03-01") && MyCalendarTable[Date] <= DATEVALUE ( "2020-03-31")
        )
    MEASURE MyFactTable[MyMeasure] =
    	CALCULATE (
    		SUM(MyFactTable[Value]),
    		FILTER(MyCalendarTable, MyCalendarTable[Date] <= maxDate) // Failed to resolve name 'maxDate'. It is not a valid table, variable, or function name.
    	)
EVALUATE
	SUMMARIZECOLUMNS(
		MyCalendarTable[Date],
		calendarFilterTable,
		"Value", IGNORE ( MyFactTable[MyMeasure] )
	)

 

 

 

Here is what the data actual looks behind and what I expected to be the output. 

 

Actual data  Expected result 
DateValue DateValue
2020-03-01100 2020-03-01100
2020-03-02200 2020-03-02200
2020-03-03300 2020-03-03300
2020-03-04400 2020-03-04400
2020-03-05500 2020-03-05 
2020-03-06600 2020-03-06 
2020-03-07700 2020-03-07 
2020-03-08800 2020-03-08 
2020-03-09900 2020-03-09 
2020-03-101000 2020-03-10 
2020-03-111100 2020-03-11 
2020-03-121200 2020-03-12 
2020-03-131300 2020-03-13 
2020-03-141400 2020-03-14 
2020-03-151500 2020-03-15 
2020-03-161600 2020-03-16 
2020-03-171700 2020-03-17 
2020-03-181800 2020-03-18 
2020-03-191900 2020-03-19 
2020-03-202000 2020-03-20 
2020-03-212100 2020-03-21 
2020-03-222200 2020-03-22 
2020-03-232300 2020-03-23 
2020-03-242400 2020-03-24 
2020-03-252500 2020-03-25 
2020-03-262600 2020-03-26 
2020-03-272700 2020-03-27 
2020-03-282800 2020-03-28 
2020-03-292900 2020-03-29 
2020-03-303000 2020-03-30 
2020-03-313100 2020-03-31 

 

Anyone that could help me with this?

4 REPLIES 4
camargos88
Community Champion
Community Champion

Hi @p0werb1 ,

 

Try creating this measure:

 

Measure =
VAR _date = DATE(2020;03;04)
RETURN CALCULATE(SUM('Table'[Value]); FILTER('Table'; 'Table'[Date] = 'Table'[Date] && 'Table'[Date] <= _date))
 
Ricardo


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



This has helped me. Thanks! I created two measures to calculate sales for a specific group of products before the campaign start and after the campaign end. Then in the Customers Table, I created calculated columns using these measures to indicates total sales before and after the campaign for each customer. Now I am wondering how can I calculate sales during the campaign? Can I create a measure with two variables, where VAR1 would be campaign start and VAR2 would be campaign finish?

Thanks for reply!

 

Yes, it is possible to use another (second) variable inside my measure like below. 

But that´s not what I really want to do. I want to reuse my maxDate variable in some way. But maybe that´s not possible?

    MEASURE MyFactTable[MyMeasure] =
    	VAR anotherMaxDate = DATEVALUE ( "2020-03-04" )
    	RETURN
    		CALCULATE (
    			SUM(MyFactTable[Value]),
    			FILTER(MyCalendarTable, MyCalendarTable[Date] <= anotherMaxDate)
    		)	

 

camargos88
Community Champion
Community Champion

@p0werb1 ,

 

Sorry, I didn't get what you want. Can you explain it ?

 

Ricardo



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors