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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.