Previous N Month Power BI
Syed Ahmed Ali ???? ????
Data Analyst @ Majid Al Futtaim | Data Analyst, BI Developer | Power BI, SQL, Python
Time Intelligence can be really tricky, but really useful for analysis. One issue I ran into with my report was that I wanted it to dynamically select dates from the previous N months. For instance, if I am in November, I want to select revenue for August, September, and October separately on different charts. I may give a date range manually in the calculate function, but it won't change with the report with time. So this is what I came up with.
In the calendar table, I added a column "Prev N Month".
To get the dates between previous month, need a Start and End Date for that month. So I get the 1st of the current month, subtract 1 and I got End Date of previous month (October).
To get the End date of previous 2nd Month (September), just subtract the number of days of the last date of October, and the end result will be End Date of September.
Similarly, got the Start and End Dates of previous 3rd Month (August). I used Switch True to define my date range. The resultant column can be used in calculate() filter context, or maybe the visual filter itself, making it very easy to filter out the data for each separate month.
Revenue =
? ? CALCULATE(
? ? ? ? SUM(Shipments[Revenue]),
? ? ? ? dim_date[Prev N Month] = "1"
? ? )