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.
This measure calculates the actual fiscal year sales up to the current month.
As you can see 'CALENDAR'[FiscalYearOrder] is the field that contains the fiscal year, so I make sure that it is equal to the MAX fiscal year, wich will be the current year.
Then I make sure that 'CALENDAR'[FiscalMonthOrder] wich is the fiscal month in order from 1 to 12, is less or equal to the actual month's fiscal month order.
CALCULATE(
SUM('FORECASTxSALES'[SALES]),
FILTER(
ALL('CALENDAR'),
(
('CALENDAR'[FiscalYearOrder] = MAX('CALENDAR'[FiscalYearOrder])) &&
( VALUE('CALENDAR'[FiscalMonthOrder]) <= (IF(VALUE(MONTH(TODAY()))>=9,VALUE(MONTH(TODAY()))-8,VALUE(MONTH(TODAY()))+4)))
)
)
)
I want to modify this measure so that it takes the maximun FiscalMonthOrder of the filtered CALENDAR table instead the actual month's fiscal month order. The problem is that in this measure I have already used ALL() to remove the filters of the CALENDAR table.
CALCULATE(
SUM('FORECASTxSALES'[SALES]),
FILTER(
ALL('CALENDAR'),
(
('CALENDAR'[FiscalYearOrder] = MAX('CALENDAR'[FiscalYearOrder])) &&
( VALUE('CALENDAR'[FiscalMonthOrder]) <= MAX('CALENDAR'[FiscalMonthOrder]))
)
)
)
The previous example would return the maximun FiscalMonthOrder of the calendar table, but if the user has filtered the FiscalMonthOrder it wouldn't impact the masure, and I want it to impact.
I want the measure to return the actual fiscal year sales up to the maximun month selected by the user.
Hello!
Assuming FiscalMonthOrder is the field the user is selecting in a slicer, I've modified your measure to the below. I created a variable to hold the selected month if the report viewer has picked one and the maximum FiscalMonthOrder (like you originally had) if the report viewer has not.
Please let me know if this works for you or if you were looking for something different. 😄
VAR SelectedMonth =
IF (
HASONEVALUE ( 'CALENDAR'[FiscalMonthOrder] ),
SELECTEDVALUE ( 'CALENDAR'[FiscalMonthOrder] ),
MAX ( 'CALENDAR'[FiscalMonthOrder] )
)
RETURN
CALCULATE (
SUM ( 'FORECASTxSALES'[SALES] ),
FILTER (
ALL ( 'CALENDAR' ),
(
( 'CALENDAR'[FiscalYearOrder] = MAX ( 'CALENDAR'[FiscalYearOrder] ) ) &&
( VALUE ( 'CALENDAR'[FiscalMonthOrder] ) <= SelectedMonth )
)
)
)
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |