Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

12 Months Rolling

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

 

Need Help
Show and Tell
Tips and Tricks
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    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.

    12-Months-Rolling

     

    Best Regards,
    Dale

9 Replies

  • Stachu's avatar
    Stachu
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Stachu
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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