cancel
Showing results for
Did you mean:

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

Helper II

## Value for last/previous month in selected year

Hi, I would like to calculate the measure [Warranty CPTV] for last month for a year selected from slicer.

If previous year is selected, the measure should show december value.

If current year is selected and we would be in January, I want the measure to show (blank).

If current year is selected and we would be between Feb-Dec, the measure should show previous month value.

I have a calendar table with year-month key (for example '202201' for all dates in Jan 2022) and month offset column (difference in months between current month and month for the calendar day).

So far, I've come up with this measure:

CALCULATE(
[Warranty CPTV] ,
'Calendar'[YearMonth] = -
)
5 REPLIES 5
Helper II

Thanks a lot - this is guiding me in the right direction. I have used this measure for last month value:

Warranty CPTV PM = CALCULATE( [Warranty CPTV] , previousmonth('Calendar'[Date] ) )

It works fine, but I have one more ask: how to show previous month at the Total level?

Responsive Resident

Hi @Krzysztof_Hyla

You could use this measure;

Measure 1=CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Measure 2=

Calculate(SUMX( VALUES (Accuracy[Product Name]), calculate (SUMX( VALUES (Accuracy[Product Name]), [Measure1] )))
)

This one is when you got grand total error then you need to created two measures one is main KPI and another is Grand total.This scenarios var not working so this i used.

Thanks,
Thennarasu
Helper II

Hi Thennarasu_R, the Measure 2 you provided will give me a sum as a total value, but I don't want to sum the values - I want to retrieve the value for last month.

Unless I'm doing something incorrectly

Responsive Resident

Hi @Krzysztof_Hyla

ok use this one,
Measure 1=Calaculate(Sum(Sales(Sales))
Meassure 2=If(Hashonefilter(Product name),Measure 1,CALCULATE( [Warranty CPTV] , previousmonth('Calendar'[Date] ) )

Thanks,
Thennarasu

Super User

@Krzysztof_Hyla , You can use a date table and time intelligence

example

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

or try offset

Power BI Offset Compare Categories, Time Intelligence MOM, QOQ, and YOY: https://youtu.be/5YMlkDNGr0U

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