cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

## Difference between two measures

Hello. I hope you are fine 🙂

I have two measures, one to know the SPLM values and the other to know the SPLY values, in the same period of time.
I want the values up to (Today - 1 day - 1 month) for SPLM and (Today - 1 day - 1 year) for SPLY.
The measurements are exactly the same, except for the time difference. Why does SPLY assume a different period of days and fetch the total for the month?

.SPLM =
Var LastValue =
CALCULATE(
LASTDATE('FAT_Values'[CALDAY]),
ALL('FAT_Values')
)
VAR LimitDate = EDATE(LastValue,-1)

RETURN
CALCULATE(
[.Values_MTD],
FILTER(
ALL('dCalendar'),
'dCalendar'[Date]<=LimitDate))

.SPLM =
Var LastValue =
CALCULATE(
LASTDATE('FAT_Values'[CALDAY]),
ALL('FAT_Values')
)
VAR LimitDate = EDATE(LastValue,-1)

RETURN
CALCULATE(
[.Values_MTD],
FILTER(
ALL('dCalendar'),
'dCalendar'[Date]<=LimitDate))

1 ACCEPTED SOLUTION
Community Support

The two measures you provided are almost identical, except for the time period they cover. The SPLM measure calculates the values up to (Today - 1 day - 1 month), while the SPLY measure calculates the values up to (Today - 1 day - 1 year).

The difference between the two measures is in the DATEADD function used in the CALCULATE function. The DATEADD function is used to subtract a certain number of days, months, or years from a given date. In the SPLM measure, the DATEADD function subtracts one month from the current date, while in the SPLY measure, it subtracts one year from the current date.

Here is the modified SPLM measure that calculates the values up to (Today - 1 day - 1 month):

``````.SPLM =
VAR LastValue =
CALCULATE(
LASTDATE('FAT_Values'[CALDAY]),
ALL('FAT_Values')
)

RETURN
CALCULATE(
[.Values_MTD],
FILTER(
ALL('dCalendar'),
'dCalendar'[Date]<=LimitDate
)
)``````

And here is the modified SPLY measure that calculates the values up to (Today - 1 day - 1 year):

``````.SPLY =
VAR LastValue =
CALCULATE(
LASTDATE('FAT_Values'[CALDAY]),
ALL('FAT_Values')
)

RETURN
CALCULATE(
[.Values_MTD],
FILTER(
ALL('dCalendar'),
'dCalendar'[Date]<=LimitDate
)
)``````

Note that the DATEADD function subtracts one month or one year from the current date, and the TODAY function returns the current date.

Best Regards,

Community Support Team _Charlotte

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

Community Support

The two measures you provided are almost identical, except for the time period they cover. The SPLM measure calculates the values up to (Today - 1 day - 1 month), while the SPLY measure calculates the values up to (Today - 1 day - 1 year).

The difference between the two measures is in the DATEADD function used in the CALCULATE function. The DATEADD function is used to subtract a certain number of days, months, or years from a given date. In the SPLM measure, the DATEADD function subtracts one month from the current date, while in the SPLY measure, it subtracts one year from the current date.

Here is the modified SPLM measure that calculates the values up to (Today - 1 day - 1 month):

``````.SPLM =
VAR LastValue =
CALCULATE(
LASTDATE('FAT_Values'[CALDAY]),
ALL('FAT_Values')
)

RETURN
CALCULATE(
[.Values_MTD],
FILTER(
ALL('dCalendar'),
'dCalendar'[Date]<=LimitDate
)
)``````

And here is the modified SPLY measure that calculates the values up to (Today - 1 day - 1 year):

``````.SPLY =
VAR LastValue =
CALCULATE(
LASTDATE('FAT_Values'[CALDAY]),
ALL('FAT_Values')
)

RETURN
CALCULATE(
[.Values_MTD],
FILTER(
ALL('dCalendar'),
'dCalendar'[Date]<=LimitDate
)
)``````

Note that the DATEADD function subtracts one month or one year from the current date, and the TODAY function returns the current date.

Best Regards,

Community Support Team _Charlotte

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

Announcements

#### Power BI Monthly Update - November 2023

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

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors