Forum Discussion
Time Intelligence in Tabular Editor
I've created a time calculation period of QTD and Quarter in Tabular Editor.
I've created Quarter as
VAR SelectedQuarterStart = CALCULATE(MAX(DocumentDate[QuarterStart]), ALLSELECTED(DocumentDate[DayOfQuarter])) VAR QuarterStartAll = MAX(DocumentDate[QuarterStart]) VAR MaxDayOfQuarter = CALCULATE(MAX(DocumentDate[DayOfQuarter]), DocumentDate[QuarterStart] = QuarterStartAll) VAR MaxDayOfQuarterSelected = CALCULATE(MAX(DocumentDate[DayOfQuarter]), ALLSELECTED(DocumentDate), DocumentDate[QuarterStart] = QuarterStartAll) VAR Result = CALCULATE( SELECTEDMEASURE(), REMOVEFILTERS(DocumentDate), DocumentDate[QuarterStart] = SelectedQuarterStart, DocumentDate[DayOfQuarter] <= MaxDayOfQuarter ) RETURN Result
Then I've created a time calculation for 'Previous Quarter' like this
VAR _selperiod = SELECTEDVALUE ( 'Time Intelligence Periods'[Calculation Period], "Selected Range" ) VAR _StartDate = CALCULATE ( MIN ( DocumentDate[Date] )) VAR _EndDate = IF ( _selperiod = "QTD", MIN ( CALCULATE ( MAX ( DocumentDate[Date] ) ), MAX ( 'Refresh Date'[LastDateWithData] ) ), CALCULATE ( MAX(DocumentDate[QuarterEnd])) ) VAR _DaysSelected = CALCULATETABLE ( VALUES ( DocumentDate[Date] ), DATESBETWEEN ( DocumentDate[Date], _StartDate, _EndDate ) ) VAR _refperiod = IF ( _selperiod IN {"QTD", "Quarter"}, "Qtrs", BLANK() ) VAR _t = FILTER ( ADDCOLUMNS ( CALCULATETABLE ( DATEADD ( DocumentDate[Date], -3, MONTH ) ), "Range", "Qtrs" ), _refperiod = [range] ) VAR _value = SELECTEDMEASURE () VAR _prevvalue = CALCULATE ( SELECTEDMEASURE (), _t ) RETURN IF ( _selperiod IN {"FYTD", "Selected Range", "MTD", "Monthly" }, BLANK(), IF ( SELECTEDMEASURENAME () = "CF Dynamic Measure", BLANK(), _prevvalue ) )
The QTD works as expected. If I'm mid quater it will show the previous period from the start of the previous quarter up to the same day as my 'current period'
When I select Quarter, it still shows the same number of days from the previous quarter rather than going to the end of the quarter.
My MTD and Monthly calculations are built the same way and they work fine, I just can't work out why the Quarter calculation isn't working as I want it too
I've actually solved my own problem. Sorry for the inconvenience everyone, but if it's bugging anyone what it was it was because I had missed a _DaysSelected from my ADDCOLUMNS section
ADDCOLUMNS ( CALCULATETABLE ( DATEADD ( DocumentDate[Date], -3, MONTH ),_DaysSelected ), "Range", "Qtrs" ),
2 Replies
- BenCollinsNew Member
I've actually solved my own problem. Sorry for the inconvenience everyone, but if it's bugging anyone what it was it was because I had missed a _DaysSelected from my ADDCOLUMNS section
ADDCOLUMNS ( CALCULATETABLE ( DATEADD ( DocumentDate[Date], -3, MONTH ),_DaysSelected ), "Range", "Qtrs" ),
- MFelixSuper User
Hi BenCollins ,
These are the best kind of issues were the users solve it's own problem. 😂 (just kidding).
Don't forget to accept your answer as solution so it can help others.