Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Alisea_MI
Advocate III
Advocate III

Datesinperiod is not filtering the period correctly.

Hi,

I am using the same datesinperiod formula to show the last thirteen calender months in two measures: KFP for a region and portfolio share of a region.

KFP for a region uses the following formula, returns the correct values and the correct period.

2.jpg
For a portfolio share I am using the formula below. It returns the correct values, however the period is wrong. Please see picture 3 below.
1.jpg
The period I expect for both measures is in the red frame below.
3.jpg

Could anyone please help me to understand why the same formula is returning different periods and how I can troubleshoot?

 

Thank you in advance!

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@Alisea_MI 

Try this.  Make a measure that does just the % calc, no date shifting.   Then you can use that in a measure with DATESINPERIOD and see if that gives us a better result.

Andel ev Portfolj East Timeline = 
VAR _today = TODAY()
VAR _lastMonthStart = DATE(YEAR(_today), MONTH(_today) -1, 1)
VAR _LastMonthEnd = EOMONTH(lastMonthStart, 0)

RETURN
CALCULATE ( [Andel ev Portfolj East], DATESINPERIOD(InvoiceFakta[InvoideDate], _lastMonthEnd, -13, MONTH ) )

The measure called in the CALCULATE will be the one that just calculates the % you are looking for.  Then this measure adjusts the timeframe.  

View solution in original post

5 REPLIES 5
jdbuchanan71
Super User
Super User

@Alisea_MI 

Try this.  Make a measure that does just the % calc, no date shifting.   Then you can use that in a measure with DATESINPERIOD and see if that gives us a better result.

Andel ev Portfolj East Timeline = 
VAR _today = TODAY()
VAR _lastMonthStart = DATE(YEAR(_today), MONTH(_today) -1, 1)
VAR _LastMonthEnd = EOMONTH(lastMonthStart, 0)

RETURN
CALCULATE ( [Andel ev Portfolj East], DATESINPERIOD(InvoiceFakta[InvoideDate], _lastMonthEnd, -13, MONTH ) )

The measure called in the CALCULATE will be the one that just calculates the % you are looking for.  Then this measure adjusts the timeframe.  

Thank you, @jdbuchanan71 ! This variant is working. I have otherwise made a workaround and created a column in the date table that sets 1 to the 13 last months and filtered my visual on it.

 

IsLast13CalenderMonths =
var _today = TODAY()
var _lastMonthStart = DATE(YEAR(_today), MONTH(_today) -1, 1)
var _lastMonthEnd = Format(EOMONTH(_lastMonthStart, 0), "YYYYMM")
var _periodstart = Format(DATE(YEAR(_today), MONTH(_today) -13, 1), "YYYYMM")
return
if('Date'[YearMonthnumber]>= _periodstart && 'Date'[YearMonthnumber]<= _lastMonthEnd, 1,0)

 

 

jdbuchanan71
Super User
Super User

What is it returning after you changed it?

Alisea_MI
Advocate III
Advocate III

@jdbuchanan71  Thank you for the tip! Unfortunately it is still returning the wrong period. 

Is datesinperiod is supposed to filter on the dates period? Can it be a bugg?

jdbuchanan71
Super User
Super User

@Alisea_MI 

I think you are getting into trouble using CALCULATE on a variable.  Try the last line of your second measure like this.

RETURN
CALCULATE ( DIVIDE ( _East, _DVK), DATESINPERIOD(InvoiceFakta[InvoideDate], _lastMonthEnd, -13, MONTH ) )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.