Show only latest quarter in a column chart; based on monthly rolling calculations

[Anonymous]

Hello Experts,

The requirement is as below:

1. In a column chart, show only the data for the latest quarter which appears through the selected date slicer. For eg: If the slicer selection is from 01-Jan-2020 to 01-Dec-2020, the latest quarter would be Q4-2020. Similarly if the selection is from 01-Jan-2019 to 01-July-2020, the latest quarter would be Q3-2020...

Here is my data setup:

1. AllDates table...which has all dates between 01-Jan-2018 till date. (No gaps in the table)

2. Events Table...which has all events that occurred between 01-Jan-2018 till date. (Gaps would be present based as events may or may not happen on a particular day. Also it has event type column to specify what type of event it is)

Columns details are as below:

AllDates table-> Date, YearQuarter, MonthYear

Events Table -> Event Number, Event Type, Event Date, No. of events(value 1 hardcoded as granularity is at event level)

What is required?

1. A measure that will calculate an average of events for rolling 12 months but should be visible at quarter level. For eg: if the sum of events for last 12 months from the max selected date is 120, the average should be 120/12 (i.e.12 months) = 10 and not 120/4 (i.e. 4 quarters) = 30

2. A column chart where the legend would be event type and values would be plotted using the rolling 12 month measure (the one obtained in 1st point), but would only display data for current quarter(calculation will include all months, but display would only be for the latest quarter). For eg: if the max selected date is 01-jan-2020, the rolling calculation would be sum of events between 01-Jan-2019 to 01-Jan-2020; divided by 12.

Thanks in advance for the assistance.

Kind Regards,

Chetan

[v-robertq-msft · Accepted solution]

Hey, @chetanamare

According to your expression, I can clearly understand your requirement, you want to get the average of a rolling year, but only show a quarter of the data in the column chart,right? This is my new DAX formula based on its original measure, you can take a look:

No. Of Events Rolling 12 Months Average1 =
var _maxselecteddate=MAXX(ALLSELECTED('AllDates'),'AllDates'[DateValue])
var _result=
CALCULATE (
    SUM ( Events[Number Of Events] ),
    DATESBETWEEN (
        'AllDates'[DateValue],
        NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( AllDates[DateValue] ) ) ),
        MAX(AllDates[DateValue])
    ))
/12
return
IF(YEAR(MAX('AllDates'[DateValue]))=YEAR(_maxselecteddate)&&
    QUARTER(MAX('AllDates'[DateValue]))=QUARTER(_maxselecteddate)&&
MAX('AllDates'[DateValue])<=_maxselecteddate,_result,BLANK())

Then I put the measure in the column chart to replace the original measure, then I guess this is what you want:

You can download my test pbix file here

Best regards

Qin Community Support _Robert Team

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Anonymous]

Hi Robert,

This perfectly serves the requirement. Appreciate your efforts !!!

Thanks a lot 🙂

Kind Regards,

Chetan

[v-robertq-msft]

Hi, @Anonymous 

If my reply helps you to solve your problem, would you like to mark my reply as a solution so that others can learn from it too?

Thanks in advance!

Best Regards,

Community Support Team _Robert Qin

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

[v-robertq-msft]

Hi, @Anonymous 

According to your two requirements,

For the first requirement:

I can use this measure to achieve this:

Rolling 12 months average =
var _maxselecteddate=MAX('AllDates'[DateValue])
var _sum=
CALCULATE(
    SUM(Events[Number Of Events]),
    FILTER(ALL('Events'),
    [Event Date]<=_maxselecteddate&&
    [Event Date]>=DATE(YEAR(_maxselecteddate)-1,MONTH(_maxselecteddate),DAY(_maxselecteddate))))
return
_sum/12

For the second requirement:

I guess you want to make the column chart display the data based on the selection of the max date of your Slicer, am I right?

In this situation, I think the only way is to set the min data of the Slicer in the same quarter as the max date. Because calculated column/table can’t be affected by Slicer, and measure can’t be placed in the Axis/legend of a chart.

I suggest you to change the Slicer type to “List” if you don’t use the min date to affect measures, like this:

And you can get what you want, like this:

You can download my test pbix file here

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

Hi Robert,

All the other charts, except the one mentioned to change, should always show the data for all the quarters selected through the slider.

Only one chart needs to be filtered to show only max selected quarter. So the option to make the slider as a list is invalid in our case.

Kind regards,

Chetan

[v-robertq-msft]

Hi, @Anonymous 

According to your description, I can roughly understand what you want to get. But I find it hard to create such a large quantity of data based on your column details. Would you like to post some sample data in table form or pbix file(without sensitive data) and your expected result(like the chart you want to get and the correct measure value based on your sample data)?

Thanks very much!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[Anonymous]

Hi Robert,

Here is the WeTransfer link to the file. https://we.tl/t-cdM7Ue8Ru7

Kind Regards,

Chetan

[amitchandak]

@Anonymous , Not very clear.

For Qtr you can try like

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))

Last complete QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))
Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER)))

For Avg

Avg YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date])) /CALCULATE(Distinctcount('Date'[Date]),DATESYTD('Date'[Date]), not(isblank(Sales[Sales Amount])))

Avg YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD('Date'[Date])) /CALCULATE(Distinctcount('Date'[Date]),DATESQTD('Date'[Date]), not(isblank(Sales[Sales Amount])))

For year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))


//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
rolling = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]>=max('Date'[Year])-2 && 'Date'[Year]<=max('Date'[Year])) )

diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839