How to display different time periods on the same chart?

[Shawry]

Hi, 

I have a Date table, which includes a column called 'Past 6 weeks or Prior Period', which essentially says 'Past 6 weeks', 'Prior Period' or 'False'. 

I'm wanting to display the volume of calls in a single chart so that you can compare the last 6 weeks call volume, against the same period last year. I'm struggling to understand how to model this and graph the different dimensions. 

I've managed to display the two separately on invidivual charts (as below) using filtering, but would ideally like to combine these into a single chart. 

Does anyone know how to tackle this issue or model this to display accordingly? 

Thanks

[sroy_16 · Accepted solution]

Alternatively you can try this as well.

You can also use these two measure and in the visual add both the measures to achieve this.

Calls Past 6 Weeks =
CALCULATE(
SUM('Calls'[Volume]),
'Date'[Past 6 weeks or Prior Period] = "Past 6 weeks"
)

Calls Same Period Last Year =
CALCULATE(
SUM('Calls'[Volume]),
'Date'[Past 6 weeks or Prior Period] = "Past 6 weeks",
SAMEPERIODLASTYEAR('Date'[Date])
)

[Shawry]

Thanks for responding. I've tried both code blocks but both don't appear to be working. I've updated the table references accordingly to be:

Calls Past 6 Weeks = 
CALCULATE(
SUM('General Enquiries - Daily'[Calls Answered]),
'Date'[Past 6 weeks or Prior Period] = "Past 6 weeks"
)

However, not returning any results in either column. 

I also tried using a Measure how getting the same results and no values are being returned. Measure is entirely blank. 

You can also see below that my 'Past 6 Weeks or Prior Period' column is returning results within my Date table. 

Any further ideas as to why this isn't working? Thanks

[v-xinruzhu-msft]

Hi,

Thanks for the solution @sroy_16  probvided, and i want to  offer some more infotmation for user to refer to.

hello @Shawry , based on your description, you can try the following measures

Calls Past 6 Weeks = 
CALCULATE(
SUM('General Enquiries - Daily'[Calls Answered]),
'Date'[Past 6 weeks or Prior Period] = "Last 6 weeks"
)

Calls Past 6 Weeks_preperiopd =
VAR _filter =
    FILTER (
        ALLSELECTED ( 'Date' ),
        [Past 6 weeks or Prior Period] = "Last 6 weeks"
    )
VAR preyear_mindate =
    EDATE ( MINX ( _filter, [Date] ), -12 )
VAR preyear_maxdate =
    EDATE ( MAXX ( _filter, [Date] ), -12 )
RETURN
    CALCULATE (
        SUM ( 'General Enquiries - Daily'[Calls Answered] ),
        DATESBETWEEN ( 'Date'[Date], preyear_mindate, preyear_maxdate )
    )

Best Regards!

Yolo Zhu

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

[sroy_16]

Hi Shwary,

Try using this code which would act as a dynamic measure and change the value dynamically.

Calls Comparison =
VAR CurrentDate = MAX('Date'[Date])
VAR IsPast6Weeks =
CALCULATE(
SUM('Calls'[Volume]),
'Date'[Past 6 weeks or Prior Period] = "Past 6 weeks"
)
VAR IsSamePeriodLastYear =
CALCULATE(
SUM('Calls'[Volume]),
'Date'[Past 6 weeks or Prior Period] = "Past 6 weeks",
SAMEPERIODLASTYEAR('Date'[Date])
)
RETURN
IF(
YEAR(CurrentDate) = YEAR(TODAY()),
IsPast6Weeks,
IsSamePeriodLastYear
)
Let me know if the solution works.