Forum Discussion

BenCollins's avatar
BenCollins
New Member
3 years ago
Solved

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

  • 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" ),

     

    • MFelix's avatar
      MFelix
      Super 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.