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 Community,
I have a matrix table that I want to be filtered by Year and Month slicer. To do this, Im using 2 date dimension tables; one is linked to the model, and the other one is disconnected.
So, in the slicer I have Year/Month of the linked calendar, and in the columns of the matrix table the month of the disconnected calendar.
The result that I want to achieve is just a simple matrix table that when I select one month in the slicer, the table shows data from 01/07 (first day of the Fiscal Year) to the max date of the slicer selection. And in the Total column the sum of the selected months.
To do this I am using Summarize, but I have a lot of formulas using the same pattern, and I think the performance of the data set is worsening. Does it make sense or do you have any alternative to achieve the same result?
Monthly_Production =
VAR maxDateSelection =
MAXX ( ALLSELECTED ( 'Calendar' ), MAX ( 'Calendar'[Fiscal MonthNumber] ) )
RETURN
SUMX (
SUMMARIZE (
VALUES ( CalendarUnrel[Fiscal Month] ),
CalendarUnrel[Fiscal Month],
"ABCD",
IF (
MAX ( 'CalendarUnrel'[Fiscal MonthNumber] ) <= maxDateSelection,
CALCULATE (
[Production],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Fiscal MonthNumber] = MAX ( 'CalendarUnrel'[Fiscal MonthNumber] )
&& 'Calendar'[Fiscal Year] = MAX ( 'Calendar'[Fiscal Year] )
)
)
)
),
[ABCD]
)
Thank you very much
Solved! Go to Solution.
@Reuben The picture is not clear so I can't see what value you have in the month filter.
I think the below code should work but might need some tweak to fit to you model. Give it a go and let me know if this doesn't work
IF (
// find if the unrelated calender is filtered or not.
ISFILTERED ( CalendarUnrel[Fiscal Month] ),
// if unrelated calender is filtered, calculate the value for a single month
CALCULATE (
[Production],
FILTER (
ALL ( 'Calendar' ),
'Calendar]r'[Fiscal MonthNumber] = MAX ( 'CalendarUnrel'[Fiscal MonthNumbe )
)
),
// if unrelated calender is not filtered, calculate the value for the total column
CALCULATE (
[Production],
FILTER (
ALL ( 'Calendar' ),
// you may have to change this to make sure the "more than" and "less than" works properly
'Calendar'[Fiscal MonthNumber] >= "001"
&& 'Calendar'[Fiscal MonthNumber] <= MAX ( 'CalendarUnrel'[Fiscal MonthNumber] )
)
)
)
Thanks
Nishant
Thanks @Nishantjain for you help.
I have followed your tips, and with few little changes I came up with the solution. Thanks again for you support!!
Production =
VAR maxmonthselected =
MAX ( 'Calendar'[Fiscal MonthNumber] )
VAR mindate =
STARTOFYEAR ( 'CalendarUnrel'[Date], "30/06" )
VAR maxdate =
MAX ( 'CalendarUnrel'[Date] )
RETURN
IF (
ISFILTERED ( CalendarUnrel[Fiscal Month] ),
IF (
MAX ( CalendarUnrel[Fiscal MonthNumber] ) <= maxmonthselected,
CALCULATE (
[Produccion],
FILTER (
ALL ( 'Calendar' ),
'Calendar'[Fiscal MonthNumber] = MAX ( 'CalendarUnrel'[Fiscal MonthNumber] )
&& 'Calendar'[Fiscal Year] = SELECTEDVALUE ( 'Calendar'[Fiscal Year] )
)
),
IF ( MAX ( CalendarUnrel[Fiscal MonthNumber] ) > maxmonthselected, BLANK () )
),
CALCULATE (
CALCULATE ( [Produccion], DATESYTD ( 'Calendar'[Date], "30/06" ) ),
FILTER ( 'Calendar', 'Calendar'[Date] <= maxdate )
)
)
Hi,
This will be a memory intensive calculation. The easiest solution is to let the user select multiple months in the slicer rather than select just one month.
@Reuben The picture is not clear so I can't see what value you have in the month filter.
I think the below code should work but might need some tweak to fit to you model. Give it a go and let me know if this doesn't work
IF (
// find if the unrelated calender is filtered or not.
ISFILTERED ( CalendarUnrel[Fiscal Month] ),
// if unrelated calender is filtered, calculate the value for a single month
CALCULATE (
[Production],
FILTER (
ALL ( 'Calendar' ),
'Calendar]r'[Fiscal MonthNumber] = MAX ( 'CalendarUnrel'[Fiscal MonthNumbe )
)
),
// if unrelated calender is not filtered, calculate the value for the total column
CALCULATE (
[Production],
FILTER (
ALL ( 'Calendar' ),
// you may have to change this to make sure the "more than" and "less than" works properly
'Calendar'[Fiscal MonthNumber] >= "001"
&& 'Calendar'[Fiscal MonthNumber] <= MAX ( 'CalendarUnrel'[Fiscal MonthNumber] )
)
)
)
Thanks
Nishant
User | Count |
---|---|
123 | |
70 | |
67 | |
58 | |
52 |
User | Count |
---|---|
183 | |
92 | |
67 | |
62 | |
52 |