CODEWITHSUNDEEP
powerbidaxmeasure
SHENOOOO
SHENOOOO

Reputation: 56

How to create a measure that calculates sum for the previous month in the Previous year?

I am trying to calculate the number of notices in the previous month in the previous year. For example, we are now in January 2024, so, I am trying to Calculate the cases that Happened in December 2022

I created a measure as follows but it keeps giving me a Blank Value

Also, I have a Master Calendar ( MasterDate), and Column I am trying to Calculate is (KPI 10 [Days OVER 3) With a 1-1 Relationship

KPI 10 TB NO Days Over 3 Last Month Previous Year = 
var m = 
IF(MONTH(TODAY()) = 1,
    CALCULATE(SUM('KPI10'[DAYS OVER 3]), FILTER(MasterDate, MasterDate[Date] = MONTH(TODAY()) - 1), FILTER(MasterDate, MasterDate[Date] = YEAR(TODAY())-2)),
    CALCULATE(SUM('KPI10'[DAYS OVER 3]), FILTER(MasterDate, MasterDate[Date] = MONTH(TODAY()) - 1), FILTER(MasterDate, MasterDate[Date] = YEAR(TODAY())-1)))
return
m

It is giving me BLANK instead of Number

Upvotes: 2

Views: 266

Answers (2)

SHENOOOO
SHENOOOO

Reputation: 56

The work around this issue is to create a calculated column in the Master Calendar Table, which gives 1 if the date is in the Previous Month in the Last Year, and the rest is 0

So, basically it is a 0 or 1 Calculated Column, and I created it as follows:

IsLastonthPreviousYear = 
var currentdate=  MAX('MasterDate'[Date]) 
var LastMonthPreviousYearEndDate=DATE(YEAR(currentdate)-1, Month(currentdate),1)-1
var LastMonthPreviousYearStartDate= DATE(YEAR(LastMonthPreviousYearEndDate),
MONTH(LastMonthPreviousYearEndDate), 1)
return
IF('MasterDate'[Date]<=LastMonthPreviousYearEndDate &&
'MasterDate'[Date]>=LastMonthPreviousYearStartDate, 1, 0)

Upvotes: 0

Ryan B.
Ryan B.

Reputation: 3665

Your filters look wrong to me.

CALCULATE(
     SUM('KPI10'[DAYS OVER 3])
     , FILTER(MasterDate, MasterDate[Date] = MONTH(TODAY()) - 1)
     , FILTER(MasterDate, MasterDate[Date] = YEAR(TODAY())-2)
)

First, MONTH() and YEAR() return integers, and I'm assuming the value in MasterDate[Date] is likely to be some kind of date data type -- so that doesn't work.

Next, you are calculating the sum of everything in KPI10[DAYS OVER 3] where two conditions are met: When MasterDate[Date] = Today's Month - 1 AND when MasterDate[Date] = Today's Year - 2 ... those two conditions aren't likely to ever both be true, right? For the most part Today's Month - 1 <> Today's Year - 2

Save yourself some trouble and do this differently. First, recognize that 1 year and 1 month ago = 13 months ago. Then do something like this:

KPI 10 TB NO Days Over 3 Last Month Previous Year = 

CALCULATE(
    SUM('KPI10'[DAYS OVER 3])
    , PARALLELPERIOD(MasterDate[Date],-13,month)
)

Upvotes: 1

Related Questions