Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

From Selected Month Previous 12 Months Values

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 ...
Need Help
Tips and Tricks
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

     

    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 ) )
    )
     
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
4 years ago

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's avatar
    Anonymous
    Not applicable
    4 years ago

    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