3 month rolling period starting with current month + filter

[razmilea94]

Hi everyone,

I am very new to PowerBI and I am trying to create a line graph for the organization I work for. Basically I have a table with the "Resources, Months (12 months), Utilization %". I have to create a 3 month rolling period starting with current month e.g. April-May-June and based on that 3 month rolling period I have to create a Threshold Breach Filter (Y/N) with the condition that if the Utilization % in that 3 month rolling period is over 100% or under 50% then Y else N. But I need this condition to be applied to the whole 3 month period not just per month so that the lines in my line graph won't come up as dots, but as lines to see the whole trend per each resource e.g. if for Resource A the utilization % for April and May is 100% (meaning it didn't breach the threshold) but for June is 115% (it breached it), then I want the filter for Threshold Breach to show as "Y" for that whole period even though it only breached it in the month of June.

At the moment I could only create a Conditional Column for Threshold Breach, but this reflects to each month which is not what I want since it's not based on the 3 month rolling period I need.

My table looks like this:

A detailed guide/help on how to achieve this would be much appreciated since I am not familiar with DAX formulas or any complex stuff in PowerBI.

Thank you for your help!

Raz M.

[amitchandak]

If you want three months in every month you view

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH)) 
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date Filer],MAX(Sales[Sales Date]),-3,MONTH))  

In case you three month, you can use relative date slicer

https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range

[razmilea94]

Hi @amitchandak ,

Thank you for your quick response, however, I don't quite understand that formula and where to input it.

Basically in my current table I have this info per each resource: 

And I need to create (I am not sure which one) either a new table to give me a 3 month rolling period starting with current month (Apr-May-June) and the Utilization Percentage to show for each month. 

Then I need a column based on this 3 month rolling period, named Threshold Breach to say Y or N if the threshold has been breached for the whole 3 month period (if more than 100% or less than 50% then Y if else N).

In regards to relative date slicer, I tried but for some reason it shows next 3 months (May-June-July) without including current month April even though I selected "Include Today" too.

I am sorry if this is confusing, is there anyway I could explain this via Zoom or somewhere else?

Thank you!