Go one month extra than selected date slicer

[Saniat]

Hi,

I am trying to calculate a measure (measure 3 in picture), which is derived from measure 1 and 2.

As you can see to calculate B, we always take one extra month of data from the previous month of measure 2.

In the pic below the user has selected dates: March, April and May.

To calculate A, we take data for these three months but for B, we take data for one extra month.

I divide A by B to get measure 3.

Is there any way to achieve this ?

Thanks

[Ashish_Mathur · Accepted solution]

Hi,

Ensure you have a Calendar Table with a relationship (Many to One and Single) to your Data Table.  Write calculated column formulas to extract Year, Month name and Month number.  Sort the Month name by the Month number.  To your slicer, drag Month name from the Calendar table and select 3 months.  Write these measures:

Total = sum(Data[Amount])

Total including additional prior month = calculate([Total],datesbetween(Calendar[Date],edate(min(calendar[date]),-1),max(calendar[date])))

Ratio = divide([Total],[Total including additional prior month])

Hope this helps.