12 Months Rolling

[chrispybacon]

Hi there,

i have following scenario. One landing page(many KPIs) ==> Magic transparent button on KPI(card visual) ==> Drill Page

At the last page the user shall see a 12 months rolling history of whatever month he selected on the landing page. 

With a measure it works fine, I simply take Calculate with Datesbetween, and a Start Date and End Date. 

Now I want to have a bar chart for my measures that displays every month, since the last month in a 12 month history. To do that my understanding is that I need a calculated column. 
So my Plan is to create a column that labels all dates that fall in this range. 

Here are my measures:
==> Start Date:

= DATEADD(
        VALUES( 'Time Dim[Date (bins)]', -12, MONTH) 

==> End Date:

= Values('Time Dim[Date (bins)])

==> Switch Column:

SWITCH(
TRUE(),
DATEDIFF( 'End Date', 'Start Date', Month) = 12, "Yes",
"No")

I also tried:

IF( 'Time Dim [Date] >= [Start Date] && 'Time Dim [Date] <= [End Date], "yes")

One thing I should mention is that, I grouped my Dates by Months. As all the data is receive is on a month level. 

Once I am on the drill page the user shall not touch a slicer (So I will sync one with the page, and make it invisible).   


Any ideas how to solve this?

BR,
Chris

[Stachu]

you definately don't need a calculated column - just create a bar chart, add months to the axis and the original measure to visual - you may want to disable the month slicer interaction so it would show whole year rather than a single month

[chrispybacon]

Hi @Stachu,

So I have for example the following measure

= Calculate(SUM( MAN_DASH[FTE DIRECT]), DATESBETWEEN( 'Time Dim [Date], Start Date, End Date)) 

When I put this as the value in a bar chart together with the Date on the axis it only returns me the month that was chosen in the slicer, of which I want to see the 12 Month history. 

I think the problem is that the filter of From and To Date is only evaluated within the measure, so it will return the full sum of the last year of that 12 month history. 

[Stachu]

which is why I wrote that

you may want to disable the month slicer interaction so it would show whole year rather than a single month
if you change the time filter in a bar chart (which you will have to do anyway if you want to show multiple months) then tha start & end will be evaluated for each individual bar - as long as each bar represents a month you're good

[chrispybacon]

@Stachu
But when I exclude the slicer on my drill page, the start and end date does not calculate. It seems like it doesn't recognise whatever I selected on my landing page. Also when I put just a year slicer on the page, and sync the month slicer from the landing page with the page, it still only recognises whatever month I selected on the landing page. Thanks for the tip though. 

Month is a requirement, there is no way around it. There are other KPIS that require it, also on the drill page the user should not have to interact with the report, meaing that there is no visible slicer on the page.

Is there maybe a work around

in which you use a calculated table and offload all dates between the Start and End Date into, and then put these dates into the visual?

Sorry for being a newbie

BR,

Chris

[v-jiascu-msft · Accepted solution]

Hi Chris,

In short, would you like to have a visual that can show 12 months data while the slicer filters one month? If so, please download the demo from the attachment. If not, please provide a sample and the expected result.

Best Regards,
Dale

[chrispybacon]

Hello Dale(@v-jiascu-msft),

thanks for providing me a sample. Yes that seems to work, however when I replicate your solution with my data it doesn't. When filtering on a month of a year I still only receive one single bar on my bar chart.
My rolling looks as follows:

FTErolling2 = IF(
    MIN(
        CV_MAN_DASHBOARD_FTE[Date]) >= EOMONTH(
            MIN(
                'Time Dimension'[Date]), -12) && MAX(
                    CV_MAN_DASHBOARD_FTE[Date]) <= MAX(
                        'Time Dimension'[Date]), SUM(CV_MAN_DASHBOARD_FTE[FTE Direct]), 0)

The picture above shows my output. The first card is the single value and the second one is the value I want to have distibuted aling the last 12 months.  For the bar chart I used the date of my FTE Table and for the slicer the date column of my time dimension.
I don't see why it didn't work for me...

Is there maybe a way in which I label my desired rows with a calculated column depending on the user output, this way I would also avoid having a bar chart that is so long, because many of the vales are filled with zeros.

FTELABEL12M = SWITCH(TRUE(),
    CV_MAN_DASHBOARD_FTE[Date] >= [StartDate(12mrolling)] && CV_MAN_DASHBOARD_FTE[Date] <= [SelectedBin], "yes",
    "no")

I tried this formula, but it gave me yes on all rows in the table

StartDate:

StartDate(12mrolling) = 
IF(HASONEVALUE('Time Dimension'[Date]), DATEADD(
    VALUES(
        'Time Dimension'[Date]), -11, MONTH), PREVIOUSYEAR('Last year'[Today]))

EndDate:

SelectedBin = SELECTEDVALUE('Time Dimension'[Date], TODAY() )

Best regards,
Chris

[chrispybacon]

@v-jiascu-msftYour solution seems to work. However depending on the table in my data model it has different behaviour. I am not sure why that is, but probably related to my date table, however this a different topic entirely. 

I will mark your answer as a solution. Thank you for your help!

Br,
Chris

[chrispybacon]

For anyone that is interested here is another way that you can solve this problem.

This also worked for me: 
http://radacad.com/dynamic-date-range-from-slicer

Br,

Chris

[v-jiascu-msft]

Hi Chris,

Thanks for sharing. Sorry for the late reply, I had a few days leaves. 

Best Regards,
Dale