Dynamic Filtering of DAX formula that it does not go beyond Current month

[Datagulf]

I have a formula that is going beyond the time I want it to. How should I limit the formula to fall in to the scope of the past to now and not in the future.  The formula is as shown below.

SPLY INVoiced = 

CALCULATE(

    [Invoice Line TOtal],

    SAMEPERIODLASTYEAR('Date'[Date])
    )

The visual shows the formula in action. Since it is the sameperiodlastyear, it shows the formula going forward to 2024. I want it to show up to May 2023. How do I limit the Orange line to be span upto May 2023, the current time.

[Datagulf · Accepted solution]

I have tried to limit it as shown below, still does not work. Any suggestions?

SPLY INVoiced =
Var Month = MONTH(TODAY())
return
CALCULATE(

    [Invoice Line TOtal],SAMEPERIODLASTYEAR('Date'[Date]),FILTER('INV1 Invoice Rows', 'INV1 Invoice Rows'[DocDate] <= TODAY()

    ))

[Datagulf · Accepted solution]

I have tried to limit it as shown below, still does not work. Any suggestions?

SPLY INVoiced =
Var Month = MONTH(TODAY())
return
CALCULATE(

    [Invoice Line TOtal],SAMEPERIODLASTYEAR('Date'[Date]),FILTER('INV1 Invoice Rows', 'INV1 Invoice Rows'[DocDate] <= TODAY()

    ))

[v-jianboli-msft · Accepted solution]

Hi @Datagulf ,

Please try:

SPLY INVoiced =
VAR _a =
    CALCULATE ( [Invoice Line TOtal], SAMEPERIODLASTYEAR ( 'Date'[Date] ) )
RETURN
    IF ( SELECTEDVALUE ( 'Date'[Date] ) <= EOMONTH ( TODAY (), 0 ), _a )

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.