Forum Discussion

PoweredOut's avatar
PoweredOut
Resolver I
3 years ago
Solved

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 ...
Need Help
  • tt_and's avatar
    tt_and
    3 years ago

    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
    )

     

     

     

tt_and's avatar
tt_and
Advocate I
3 years ago

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
    )

 

 

 

PoweredOut's avatar
PoweredOut
Resolver I
3 years ago

Thank you so much. Returns a reult in less than a second!