Forum Discussion
12 Months Rolling
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
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
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
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,
DaleHello 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,
Chrisv-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