Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

Reply
PoweredOut
Resolver I
Resolver I

Switch calculations on a certain date

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 ) )​  

 

 

 

  • Here I added another column to show dates on and after the 15th. 

 

 

Amount After 15th of current month = 
CALCULATE ( [Amount], KEEPFILTERS ( 'DIM Date'[Completed Month on after 15th] = TRUE ) )​  

 

 

  • In the Sales fact table I added a date column that shows the 15th of the month of the sale. 

 

 

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

1 ACCEPTED SOLUTION
tt_and
Helper I
Helper I

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
    )

 

 

 

View solution in original post

2 REPLIES 2
tt_and
Helper I
Helper I

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! 

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

Top Solution Authors
Top Kudoed Authors