Value for last/previous month in selected year

[Krzysztof_Hyla]

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] = -1 

      )

[Krzysztof_Hyla]

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?

[Thennarasu_R]

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 

[Krzysztof_Hyla]

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

[Thennarasu_R]

 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

[amitchandak]

@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