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.
Hi guys
I'm trying to calculate the current year, current month and current week, ignoring the filter year and month on the report.
They work only if i don't choose any year and/or month in the report filter.
Solved! Go to Solution.
Hey @pedroccamaraDBI. Sorry, I think I missed the part about ignoring the month filter also. I attached a .pbix here with mock data, and added: ALL('Tab Dates' ) in each "VAR Result =", for example "VAR Result = CALCULATE( [Net Sales], ALL('Tab Dates' ), 'Tab Dates'[Year] = MaxYear )". So the measures look as follows:
Current Year Sales =
VAR MaxDateWithSales = CALCULATE( MAX( 'Sales'[Order Date] ), ALL('Sales') )
VAR MaxYear = YEAR(MaxDateWithSales)
VAR Result =
CALCULATE( [Net Sales], ALL('Tab Dates' ), 'Tab Dates'[Year] = MaxYear )
RETURN
Result
Current Month Sales =
VAR MaxDateWithSales =
CALCULATE( MAX( 'Sales'[Order Date] ), ALL( 'Sales' ) )
VAR MaxYear = YEAR( MaxDateWithSales )
VAR MaxMonth = MONTH( MaxDateWithSales )
VAR Result =
CALCULATE(
[Net Sales],
ALL('Tab Dates'),
'Tab Dates'[Year] = MaxYear,
'Tab Dates'[MonthOfYear] = MaxMonth
)
RETURN
Result
Current Week Sales =
VAR MaxDateWithSales =
CALCULATE( MAX( 'Sales'[Order Date] ), ALL( 'Sales' ) )
VAR MaxYear = YEAR( MaxDateWithSales )
VAR MaxWeek =
CALCULATE(
VALUES('Tab Dates'[ISO Weeknumber] ),
FILTER( ALL('Tab Dates'), 'Tab Dates'[Date] = MaxDateWithSales )
)
VAR Result =
CALCULATE(
[Net Sales],
ALL('Tab Dates' ),
'Tab Dates'[Year] = MaxYear,
'Tab Dates'[ISO Weeknumber] = MaxWeek
)
RETURN
Result
And the results look like:
Does that work?
Hello @pedroccamaraDBI.
Looking at your first measure, Current Year Sales: The "MAX('Tab Dates'[Year])" is calculating the max year in the current filter context. So if you have a report filter it will only calculate the maxmium year that is visible in that filter.
I think you instead want something that looks like this:
VAR MaxYear = CALCULATE( MAX( 'Tab Dates'[Year] ), ALL('Tab Dates' ) )
VAR Result =
CALCULATE( [Net Sales], 'Tab Dates'[Year] = MaxYear )
RETURN
Result
This will calculate the max year in your date table, ignoring the current filter context. And your other monthly and weekly measures will use the same concept.
-Steve
Hey @SteveHailey
Thank you so much for your answer. Let me ask you 2 things:
1. Because it would be a diferent measure how would you do it for current month, knowing that we have to filter besides the month, the year also.
2. How will it work, this current month measure, because the max month my not be the last with data, right? My calendar table has all the other months after that one, you know?
Thanks a lot and looking forward to see your answer
Best regards
Hey @pedroccamaraDBI. It sounds like you're defining the current date as the last date with sales data. In that case, I think something like the following should work:
Current Year Sales =
VAR MaxDateWithSales = CALCULATE( MAX( 'Sales'[Order Date] ), ALL('Sales') )
VAR MaxYear = YEAR(MaxDateWithSales)
VAR Result =
CALCULATE( [Net Sales], 'Tab Dates'[Year] = MaxYear )
RETURN
Result
Current Month Sales =
VAR MaxDateWithSales =
CALCULATE( MAX( 'Sales'[Order Date] ), ALL( 'Sales' ) )
VAR MaxYear = YEAR( MaxDateWithSales )
VAR MaxMonth = MONTH( MaxDateWithSales )
VAR Result =
CALCULATE(
[Net Sales],
'Tab Dates'[Year] = MaxYear,
'Tab Dates'[MonthOfYear] = MaxMonth
)
RETURN
Result
Current Week Sales =
VAR MaxDateWithSales =
CALCULATE ( MAX ( 'Sales'[Order Date] ), ALL ( 'Sales' ) )
VAR MaxYear =
YEAR ( MaxDateWithSales )
VAR MaxWeek =
CALCULATE (
VALUES ( 'Tab Dates'[ISO Weeknumber] ),
FILTER ( 'Tab Dates', 'Tab Dates'[Date] = MaxDateWithSales )
)
VAR Result =
CALCULATE (
[Net Sales],
'Tab Dates'[Year] = MaxYear,
'Tab Dates'[ISO Weeknumber] = MaxWeek
)
RETURN
Result
Hello @SteveHailey
I've just tested the first one, current year sales, and it didn't ignore my year and month filters as it should.
Just a reminder, i want to be able to see, everyday when i open this report, the total sales of this year, quarter, month and week.
Maybe it's missing somewhere, ALL ( Dates)...which i don't know where to put it in your measure.
Thanks a lot
Hey @pedroccamaraDBI. Sorry, I think I missed the part about ignoring the month filter also. I attached a .pbix here with mock data, and added: ALL('Tab Dates' ) in each "VAR Result =", for example "VAR Result = CALCULATE( [Net Sales], ALL('Tab Dates' ), 'Tab Dates'[Year] = MaxYear )". So the measures look as follows:
Current Year Sales =
VAR MaxDateWithSales = CALCULATE( MAX( 'Sales'[Order Date] ), ALL('Sales') )
VAR MaxYear = YEAR(MaxDateWithSales)
VAR Result =
CALCULATE( [Net Sales], ALL('Tab Dates' ), 'Tab Dates'[Year] = MaxYear )
RETURN
Result
Current Month Sales =
VAR MaxDateWithSales =
CALCULATE( MAX( 'Sales'[Order Date] ), ALL( 'Sales' ) )
VAR MaxYear = YEAR( MaxDateWithSales )
VAR MaxMonth = MONTH( MaxDateWithSales )
VAR Result =
CALCULATE(
[Net Sales],
ALL('Tab Dates'),
'Tab Dates'[Year] = MaxYear,
'Tab Dates'[MonthOfYear] = MaxMonth
)
RETURN
Result
Current Week Sales =
VAR MaxDateWithSales =
CALCULATE( MAX( 'Sales'[Order Date] ), ALL( 'Sales' ) )
VAR MaxYear = YEAR( MaxDateWithSales )
VAR MaxWeek =
CALCULATE(
VALUES('Tab Dates'[ISO Weeknumber] ),
FILTER( ALL('Tab Dates'), 'Tab Dates'[Date] = MaxDateWithSales )
)
VAR Result =
CALCULATE(
[Net Sales],
ALL('Tab Dates' ),
'Tab Dates'[Year] = MaxYear,
'Tab Dates'[ISO Weeknumber] = MaxWeek
)
RETURN
Result
And the results look like:
Does that work?
This is awesome Steve.
Thank you so much
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
19 | |
15 |