Forum Discussion

ApplePie's avatar
ApplePie
New Member
2 years ago

Display only dates with existing data when calculating measure

I have a data set with values pulled from a timeseries table via a measure called [Avg]. I am trying to calculate the annual average by date from a data set that has a data result once per quarter. See example:

DateData[Avg]
2/12/201921
5/14/201950
8/13/201955
11/12/201932
2/15/202020

 

I have a code that correctly calculates the annual running average here:

 
VAR Duration = 4
VAR LastSelectDate = MAX(Dates[Date])
VAR Period = DATESINPERIOD( Dates[Date], LastSelectDate, -Duration, QUARTER)
VAR LRAA =
IF(COUNTROWS(Period)>364,
      CALCULATE (
        AVERAGEX ( FILTER(ALLSELECTED(Dates), Dates[Date] <= LastSelectDate),
        [Avg]),
      Period),
     BLANK())
RETURN
LRAA
 
The issue I am having is that My output table returns a average calculation for every single date in the quarter, so my chart visual has horizontal sections in the chart (obv because the average isnt changing). 
 

 

I want it to have one point for the avarage only where there is a date with data. I have played around with the filters but can't get it working while still calulating the annual average. Any tips?

Need Help
Tips and Tricks

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    2 years ago

    ApplePie , Assuming , You table is having a date column

     

    VAR Duration = 4
    VAR LastSelectDate = MAX(Dates[Date])
    VAR Period = DATESINPERIOD( Dates[Date], LastSelectDate, -Duration, QUARTER)
    VAR LRAA =
    IF(COUNTROWS(Period)>364,
    CALCULATE (
    AVERAGEX ( FILTER(ALLSELECTED(Dates), Dates[Date] <= LastSelectDate),
    [Avg]),
    Period, not(ISBLANK(Table[Date])) ),
    BLANK())
    RETURN
    LRAA

     

     

    Or use this with date from your Table on axis

    VAR Duration = 4
    VAR LastSelectDate = MAX(Table[Date])
    VAR Period = Date(Year(LastSelectDate)-1, month(LastSelectDate), day(LastSelectDate))
    VAR LRAA =
    CALCULATE (
    AVERAGEX ( FILTER(ALLSELECTED(Dates), Dates[Date] <= LastSelectDate),
    [Avg]),
    Period, not(ISBLANK(Table[Date])) )
    RETURN
    LRAA