Forum Discussion

wskallmeyer's avatar
wskallmeyer
Advocate II
7 years ago
Solved

Stacked area chart using dynamic date range

All,

 

I am showing a running total of counts for the past 2 years.  The chart is getting too wide for my customer.  I need to keep the totals based on the entire time period.  But I would like to only show the last 12 months.  The X-axis has a Start and End Date but I don't see a way to make it dynamic based on my live data.  I could hard code the dates but then I would have to change it manually each month as I am in the screen shot below.

 

 

Any ideas?  Or different charts I should use?

 

I am using the latest PBIRS (on premises).

 

Thanks in advance, Scott

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi wskallmeyer 

    You may create a measure like below and use it in visual level filter and set it =1.

    Last 12 Month =
VAR EndThisMonth =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 31 )
VAR Enddate_LastMonth =
    EDATE ( EndThisMonth, -1 )
RETURN
    IF (
        MAX ( 'Date'[Date] ) <= Enddate_LastMonth
            && MAX ( 'Date'[Date] ) > EDATE ( EndThisMonth, -13 ),
        1
    )

    Regards,

1 Reply

  • v-cherch-msft's avatar
    v-cherch-msft
    Microsoft Employee
    7 years ago

    Hi wskallmeyer 

    You may create a measure like below and use it in visual level filter and set it =1.

    Last 12 Month =
VAR EndThisMonth =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 31 )
VAR Enddate_LastMonth =
    EDATE ( EndThisMonth, -1 )
RETURN
    IF (
        MAX ( 'Date'[Date] ) <= Enddate_LastMonth
            && MAX ( 'Date'[Date] ) > EDATE ( EndThisMonth, -13 ),
        1
    )

    Regards,