Rolling 3 Months Data

[gvb238]

Hi Team,

I Have one Requirement of Rolling 3 Months.  for this Requirement i have Date & Amount fields. For example If i filter June 2018 then it will show like this

Month&Year           AMT

May 2018                5000

April 2018                4500

March 2018              6000

Regards,

Veera

[v-juanli-msft · Accepted solution]

Hi @gvb238

Here is an example

1.create a date table and manage relationships between the two table

date table = DISTINCT(Table1[date]) 

2. add 'date table' [date] to the slicer, then create measures as below

selected month = SELECTEDVALUE('date table'[date]) 

month difference = DATEDIFF(MAX([date]),[selected month],MONTH) 

if last three month = IF([month difference]>=1&&[month difference]<=3,1,0)

3.then add the "if last three month" to the Visual Level filter, and set the condition to "show items when value is 1".

Best Regards

Maggie

[Anonymous]

Hi, tried your solution, and sorry does not work 😞

How can I get the selected date to every row in the table? I have it only on the row related to the selection, and then the next steps can be aplied 😞

Thank you for help ...

[Anonymous]

The problem is with DATEDIFF funkcion in the previous solution, I get the result 0 in the row of selected Month, other months have blank cell 😞

[Anonymous]

Hi, tried your solution, and sorry does not work 😞

How can I get the selected date to every row in the table? I have it only on the row related to the selection, and then the next steps can be aplied 😞

Thank you for help ...

[Anonymous]

@gvb238

1) I would sort the list and rank them by date(mm-yyyy).

2) CALCULATE(SUM(x), FILTER(table, table[mnth rank]>= table[mnth rank]-3 && table[mnth rank] <= table[mnth rank])

[gvb238]

In my Datasource I don't have any Rank field. How to create rank to that Month&Year (Dimension) and what bases i can create rank.