Forum Discussion

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago
Solved

Filter specific period for Area Chart with DAX

Hi 
I'm trying to show only the last 10 months in the area chart, Measure already filtered as shown below I want to add another one to retrieve only the last 10 months.

EVC Cumm. =
var lastdateevc = CALCULATE(LASTDATE(EVCDis[DatesID]),EVCDis[DisUnit]>0)
var m10 = edate(lastdateevc,-10)
return
IF(max(DatesID[DatesID])<lastdateevc ,BLANK(),
CALCULATE(
  SUM(EVCDis[DisUnit]),
  FILTER(
      ALLSELECTED(DatesID[DatesID]),
      DatesID[DatesID]<= MAX(DatesID[DatesID]))))
 
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi Syndicate_Admin ,

    According to your description, I create a sample.

    EVCDis table:

    DatesID table:

    Here's my solution, create a measure.

    Measure =
VAR lastdateevc =
    CALCULATE (
        LASTDATE ( EVCDis[DatesID] ),
        FILTER ( ALL ( 'EVCDis' ), EVCDis[DisUnit] > 0 )
    )
VAR m10 =
    EDATE ( lastdateevc, -10 )
RETURN
    IF (
        MAX ( DatesID[DatesID] ) < m10
            || MAX ( 'DatesID'[DatesID] ) > lastdateevc,
        BLANK (),
        CALCULATE (
            SUM ( EVCDis[DisUnit] ),
            FILTER (
                ALLSELECTED ( DatesID[DatesID] ),
                DatesID[DatesID] <= MAX ( DatesID[DatesID] )
            )
        )
    )

    Put DatesID column from DatesID table and the measure in a chart, get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Syndicate_Admin ,

    According to your description, I create a sample.

    EVCDis table:

    DatesID table:

    Here's my solution, create a measure.

    Measure =
VAR lastdateevc =
    CALCULATE (
        LASTDATE ( EVCDis[DatesID] ),
        FILTER ( ALL ( 'EVCDis' ), EVCDis[DisUnit] > 0 )
    )
VAR m10 =
    EDATE ( lastdateevc, -10 )
RETURN
    IF (
        MAX ( DatesID[DatesID] ) < m10
            || MAX ( 'DatesID'[DatesID] ) > lastdateevc,
        BLANK (),
        CALCULATE (
            SUM ( EVCDis[DisUnit] ),
            FILTER (
                ALLSELECTED ( DatesID[DatesID] ),
                DatesID[DatesID] <= MAX ( DatesID[DatesID] )
            )
        )
    )

    Put DatesID column from DatesID table and the measure in a chart, get the correct result.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.