cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.