Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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 | |||
Date | Value | Date | Value | |
2020-03-01 | 100 | 2020-03-01 | 100 | |
2020-03-02 | 200 | 2020-03-02 | 200 | |
2020-03-03 | 300 | 2020-03-03 | 300 | |
2020-03-04 | 400 | 2020-03-04 | 400 | |
2020-03-05 | 500 | 2020-03-05 | ||
2020-03-06 | 600 | 2020-03-06 | ||
2020-03-07 | 700 | 2020-03-07 | ||
2020-03-08 | 800 | 2020-03-08 | ||
2020-03-09 | 900 | 2020-03-09 | ||
2020-03-10 | 1000 | 2020-03-10 | ||
2020-03-11 | 1100 | 2020-03-11 | ||
2020-03-12 | 1200 | 2020-03-12 | ||
2020-03-13 | 1300 | 2020-03-13 | ||
2020-03-14 | 1400 | 2020-03-14 | ||
2020-03-15 | 1500 | 2020-03-15 | ||
2020-03-16 | 1600 | 2020-03-16 | ||
2020-03-17 | 1700 | 2020-03-17 | ||
2020-03-18 | 1800 | 2020-03-18 | ||
2020-03-19 | 1900 | 2020-03-19 | ||
2020-03-20 | 2000 | 2020-03-20 | ||
2020-03-21 | 2100 | 2020-03-21 | ||
2020-03-22 | 2200 | 2020-03-22 | ||
2020-03-23 | 2300 | 2020-03-23 | ||
2020-03-24 | 2400 | 2020-03-24 | ||
2020-03-25 | 2500 | 2020-03-25 | ||
2020-03-26 | 2600 | 2020-03-26 | ||
2020-03-27 | 2700 | 2020-03-27 | ||
2020-03-28 | 2800 | 2020-03-28 | ||
2020-03-29 | 2900 | 2020-03-29 | ||
2020-03-30 | 3000 | 2020-03-30 | ||
2020-03-31 | 3100 | 2020-03-31 |
Anyone that could help me with this?
Hi @p0werb1 ,
Try creating this measure:
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)
)
User | Count |
---|---|
21 | |
19 | |
12 | |
10 | |
9 |
User | Count |
---|---|
30 | |
26 | |
15 | |
13 | |
10 |