Roling 12 month, Month and Year filters

[Mateja]

Hey everyone,

I needs to recostruct the dashboard in my company. I can only see the dashboard on the Power BI service and cant download the .pbix. 
A date table was made with Rolling 12 months and some other fileds that could be used as filters (see the picture). I can see that these are not measures but are imported or most likely created in Power Query (as the data is imported from excel file that has 3 sheets imported in Power BI).
Is there a code one could write that will behave dynamic and reflect the actual values for these 3 presented  approaches (as these values change every month). 

I just started working and learning DAX so M is still out of my reach 

Thank you!

[jennratten · Accepted solution]

On yes, it is very easy to do with Power Query. You just select the column that has the date, go to the Add Column, select the Date group and choose the type of date column you'd like to add. 

[v-eqin-msft · Accepted solution]

Hi @Mateja ,

If you want to dynamically set a text flag for each year-month based on current date, please create a measure:

Flag = 
var _diff=DATEDIFF(MAX('Table'[Month_Year]),TODAY(),MONTH)
return SWITCH(TRUE(), _diff=0,"Current", _diff>0 && _diff<=12,"Rolling 12 months", _diff>=13, "Out(Past)","Out(Future)")

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

[Mateja]

Thank you, a very nice approach!

[jennratten]

Hello - here are some fantastic references for the items you requested, which include sample files that you can download.

Rolling 12 Months Average

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/ 

Time Patterns

https://www.daxpatterns.com/time-patterns/ 

Creating a Simple Date Table Using DAX

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/ 

[Mateja]

Hi jennratten

Thank you for the answer! I will def bookmar these.
I know about possibilities of these calculation in Dax but is it possible to make them in Power Query, so they are dynamic and update with refresing of the database. For example the Month Class calculation: The Data Month is the current month and so on and these info are stored in a column that changes every month because the months change.
I think that these calculation are a part of a table, as a column as I cannot see the measure icon that is present in Power Bi.

[jennratten · Accepted solution]

On yes, it is very easy to do with Power Query. You just select the column that has the date, go to the Add Column, select the Date group and choose the type of date column you'd like to add. 

[Mateja]

Thank you! That helped to make different columns. 

But if I wanted to make column that is dynamic and where it would say Last 12 month for the months that are just before the one we are in and for all the other it would say OUT/NO how would you do it?