cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
Alisea_MI
Resolver I
Resolver I

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
Resolver I
Resolver I

@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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

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