From Selected Month Previous 12 Months Values

[Anonymous]

Hi Friends,

I am facing issue in one of my report. I have 3 tables

Fact Incident Table

Fact Hour Table

Dim Date Table

Incident Table

Date	Incidents
27-Jun-2021	75
01-Jun-2021	45
23-Jan-2021	85
20-Jan-2022	77
27-Mar-2021	54
01-Feb-2021	94
19-Jun-2020	79
16-Nov-2020	86
29-Sep-2021	82
11-Nov-2021	65

Hour Table

Date	Hours
29-Jun-2021	1200
10-Jun-2021	3221
23-Jan-2021	1319
20-Jan-2022	1473
27-Mar-2021	1224
01-Feb-2021	1144
19-Jun-2020	1177
16-Nov-2020	1144
29-Sep-2021	1198
11-Nov-2021	1005

And we have convetional DATE table as usual.

Now i want to create a calculation which shows data for previous 12 months from the selected month.

For an example, i have a month slicer from DATE table and lets say if i select Nov 2021 it has to show the data for previous 12 months including Nov 2021 i.e. from Dec 2020 to Nov 2021 and Result should be like the below.

Month	Incidents	Hours
Dec-20	-	-
Jan-21	85	1,319
Feb-21	94	1,144
Mar-21	54	1,224
Apr-21	-	-
May-21	-	-
Jun-21	120	4,421
Jul-21	-	-
Aug-21	-	-
Sep-21	82	1,198
Oct-21	-	-
Nov-21	65	1,005

Please help me on this

@amitchandak @Greg_Deckler @diex @aj1973 @BA_Pete @Jihwan_Kim 

[Jihwan_Kim · Accepted solution]

Incidents: =

VAR slicerselect =

MAX ( Slicer_Dates[Date] )

VAR before12months = DATE( YEAR(slicerselect)-1, MONTH(slicerselect), DAY(slicerselect))

VAR datesperiod12months =

DATESBETWEEN ( Dates[Date], before12months+1, slicerselect )

RETURN

CALCULATE (

SUM ( Incident[Incidents] ),

KEEPFILTERS ( DATESBETWEEN ( Dates[Date], before12months, slicerselect ) )

)

Hours: =

VAR slicerselect =

MAX ( Slicer_Dates[Date] )

VAR before12months = DATE( YEAR(slicerselect)-1, MONTH(slicerselect), DAY(slicerselect))

VAR datesperiod12months =

DATESBETWEEN ( Dates[Date], before12months+1, slicerselect )

RETURN

CALCULATE (

SUM ( 'Hour'[Hours] ),

KEEPFILTERS ( DATESBETWEEN ( Dates[Date], before12months, slicerselect ) )

)

[Anonymous]

Thanks @Jihwan_Kim it is working perfectly for me.

Thank you very much for coming up with sample file.

[amitchandak]

@Anonymous , when you select a month to want to display 12 months then you need an independent date table. But if need only total of 12 months you can use rolling

example

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

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(today(),-12)+1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

refer video for diff

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI

[Anonymous]

Hi @amitchandak 

Thanks for the reply, but i am getting different result like for all the months i am getting same result from Hrs table.

It would be great if you can share the sample PBI file here.

Thanks in advance