Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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])
)
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])
)
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
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.
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.
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
114 | |
89 | |
88 | |
35 | |
35 |
User | Count |
---|---|
154 | |
100 | |
83 | |
63 | |
54 |