Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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.
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.
The period I expect for both measures is in the red frame below.
Could anyone please help me to understand why the same formula is returning different periods and how I can troubleshoot?
Thank you in advance!
Solved! Go to Solution.
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.
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.
What is it returning after you changed it?
@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?
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 ) )
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!