March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello
I am looking to optimise a measure(s) that i have created.
Outcome I would like: I have a matrix table that shows amounts. These amounts should switch on the 15th of each month. Before the 15th of each month it shows amounts up to the month before last. Example up to March 15th, it show all amounts up to the end of January. After the 15th, it shows amounts up to the end of February.
I have it working, but I'm not happy with the length of time it takes to load. Curently it's 12 seconds.
The measure is a combination of measures.
Measures
Amount =
SUM ( 'Sales'[Amount] )
Here I added a column to the Date table that shows completed months to show before the 15th.
Amount Before 15th of Current Month =
CALCULATE ( [Amount], KEEPFILTERS ( 'DIM Date'[Completed Month] = TRUE ) )
Amount After 15th of current month =
CALCULATE ( [Amount], KEEPFILTERS ( 'DIM Date'[Completed Month on after 15th] = TRUE ) )
Amount Switch =
IF (
MAX ( 'DIM Date'[Date]) >= MAX('Sales'[Period Change ]),
[Amount After 15th of current month],
[Amount Before 15th of Current month]
)
I suppose my questions would be can I optimise these and is it good practice to build multiple measures on a base measure, in this case the amount measure?
Thanking in advance
Solved! Go to Solution.
How about something like this to avoid the switch?
Edit: Changed >= to < in periodBefore variable
One measure instead of two =
VAR periodBefore =
IF (
MAX ( 'DIM Date'[Date]) < MAX('Sales'[Period Change ]),
1,
0
)
VAR periodAfter =
IF (
MAX ( 'DIM Date'[Date]) >= MAX('Sales'[Period Change ]),
1,
0
)
RETURN
(
CALCULATE ( [Amount], KEEPFILTERS ( 'DIM Date'[Completed Month] = TRUE ) ) * periodBefore
)
+
(
CALCULATE ( [Amount], KEEPFILTERS ( 'DIM Date'[Completed Month on after 15th] = TRUE ) ) * periodAfter
)
How about something like this to avoid the switch?
Edit: Changed >= to < in periodBefore variable
One measure instead of two =
VAR periodBefore =
IF (
MAX ( 'DIM Date'[Date]) < MAX('Sales'[Period Change ]),
1,
0
)
VAR periodAfter =
IF (
MAX ( 'DIM Date'[Date]) >= MAX('Sales'[Period Change ]),
1,
0
)
RETURN
(
CALCULATE ( [Amount], KEEPFILTERS ( 'DIM Date'[Completed Month] = TRUE ) ) * periodBefore
)
+
(
CALCULATE ( [Amount], KEEPFILTERS ( 'DIM Date'[Completed Month on after 15th] = TRUE ) ) * periodAfter
)
Thank you so much. Returns a reult in less than a second!
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
125 | |
85 | |
69 | |
54 | |
45 |
User | Count |
---|---|
204 | |
106 | |
98 | |
65 | |
54 |