cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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?

3 REPLIES 3
Highlighted
Community Champion
Community Champion

Re: All days on month SUM up to certain date

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!
Ricardo

Highlighted
Frequent Visitor

Re: All days on month SUM up to certain date

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

 

Highlighted
Community Champion
Community Champion

Re: All days on month SUM up to certain date

@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!
Ricardo

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors