Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to create a budget table based on sales values from the previous year, but would like to exclude any rows that might have blank Annual Budget values for the current year because the particular combination of the joined rows might not have had sales in the previous year.
Is it possible to use ADDCOLUMNS and CROSSJOIN to create a new table, but excludes rows in that new table based on the results of the CALCULATE function?
See here:
Annual Budget =
ADDCOLUMNS(
FILTER(
CROSSJOIN(
DISTINCT( Dates[Month MMM] ),
DISTINCT( Dates[Year] ),
VALUES( SalesRep[Full Name] ),
VALUES( JobType[JobType1] )
),
Dates[Year] = MAX( Dates[Year] ) //to always keep current year
),
"Annual Budget",
CALCULATE(
[Total Sales],
DATEADD(
Dates[Date],
-1,
YEAR
)
)
)
And see sample results:
Annual Budget | Full Name | Job Type1 | Month MMM | Year |
| Tom | Local | Jan | 2020 |
35,000 | Dave | Local | Jan | 2020 |
50,000 | Sherry | Local | Jan | 2020 |
| Alice | Local | Jan | 2020 |
45,000 | Talia | Local | Jan | 2020 |
If neither Tom nor Alice had sales for:
Is it possible to exclude those rows in the new table?
Thanks in advance.
Solved! Go to Solution.
This should be possible, in theory you should be able to wrap your existing expression in FILTER()
eg.
Annual Budget =
FILTER(
ADDCOLUMNS(
FILTER(
CROSSJOIN(
DISTINCT( Dates[Month MMM] ),
DISTINCT( Dates[Year] ),
VALUES( SalesRep[Full Name] ),
VALUES( JobType[JobType1] )
),
Dates[Year] = MAX( Dates[Year] ) //to always keep current year
),
"Annual Budget",
CALCULATE(
[Total Sales],
DATEADD(
Dates[Date],
-1,
YEAR
)
)
),
NOT( ISBLANK( [Annual Budget] ) )
)
hi @JRHans09
You could add a FILTER in the for mula as below:
Regards,
Lin
@JRHans09 wrote:
@d_gosbell - thanks, your answer is virtually the same.
But they are not exactly the same. I am using NOT( ISBLANK( ... ) ) as it will ONLY exclude blanks. if you do <> BLANK() it will exclude blanks and 0 values (since blanks coalesce to a numeric value of 0 when you compare them to a numeric measure or column)
hi @JRHans09
You could add a FILTER in the for mula as below:
Regards,
Lin
This should be possible, in theory you should be able to wrap your existing expression in FILTER()
eg.
Annual Budget =
FILTER(
ADDCOLUMNS(
FILTER(
CROSSJOIN(
DISTINCT( Dates[Month MMM] ),
DISTINCT( Dates[Year] ),
VALUES( SalesRep[Full Name] ),
VALUES( JobType[JobType1] )
),
Dates[Year] = MAX( Dates[Year] ) //to always keep current year
),
"Annual Budget",
CALCULATE(
[Total Sales],
DATEADD(
Dates[Date],
-1,
YEAR
)
)
),
NOT( ISBLANK( [Annual Budget] ) )
)
@JRHans09 wrote:
@d_gosbell - thanks, your answer is virtually the same.
But they are not exactly the same. I am using NOT( ISBLANK( ... ) ) as it will ONLY exclude blanks. if you do <> BLANK() it will exclude blanks and 0 values (since blanks coalesce to a numeric value of 0 when you compare them to a numeric measure or column)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |