Viewing N Months In Power BI

Jason Thomas shows a nice method to combine data for the last N months along with a month-by-month breakdown using a single date dimension:

Create 3 measures as shown below, and then add those 3 measures in the report along with a month slicer as shown below. You can change the month in the slicer and verify that the measure values change for the selected month.

Sales (Selected Month)
 = SUM ( Sales[Sales] )

Sales Last Year
 = CALCULATE ( SUM ( Sales[Sales] )SAMEPERIODLASTYEAR ( ‘Date'[Date] ) )

Sales YTD 
TOTALYTD ( SUM ( Sales[Sales] ), ‘Date'[Date] )

This is the first time I’ve seen a What If parameter in use.  Very interesting.

Related Posts

Calculating Future Revenue Streams With Phasing

Matt Allington walks us through a somewhat complicated scenario with income stream projections but inconsistent monthly payout rates: The first thing I decided to do was work out which was the first month of the policy.  I used LOOKUPVALUE to do this because the Calendar table was not connected to the policy table.  Instead of […]

Read More

Sorting When Your Measure Is Not In The Visual

Kasper de Jonge shows us different ways of sorting a visual by some unrelated measure: So lets start with the simple one, I want to sort a chart on a measure not part of the visual. Let’s take this visual: Now instead of sorting by OrderQuantity I want to sort by the ListPrice. The trick […]

Read More


March 2018
« Feb Apr »