Previous N Month Power BI

Previous N Month Power BI

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.

No alt text provided for this image

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"
? ? )         
No alt text provided for this image

要查看或添加评论,请登录

Syed Ahmed Ali ???? ????的更多文章

  • Power BI Dynamic Top N (Include Others) using Parameter

    Power BI Dynamic Top N (Include Others) using Parameter

    A few months ago, I worked with a client who had a unique requirement: they needed a filter that could dynamically…

    10 条评论
  • A Comprehensive Guide to Power BI Error Bars

    A Comprehensive Guide to Power BI Error Bars

    Introduction Error bars are essential for visualizing data variability and uncertainty, making them invaluable for more…

    5 条评论
  • Mastering Power BI DAX: IsInScope

    Mastering Power BI DAX: IsInScope

    When working with hierarchies in Power BI, managing totals and granular levels can be challenging. One function that…

    7 条评论
  • Power BI: Highlighting Selected Items

    Power BI: Highlighting Selected Items

    Highlighting specific items in Power BI is a powerful way to enhance data analysis, especially when comparing these…

  • Power BI Dynamic Formatting Trick

    Power BI Dynamic Formatting Trick

    Sharing a cool trick I have been using for dynamic formatting in Power BI which I don't see many people use, and it is…

  • Power BI Custom Label Trick (Dynamic Selection)

    Power BI Custom Label Trick (Dynamic Selection)

    In the realm of data visualization, the utilization of custom labels has unlocked a myriad of possibilities. However, a…

  • Power BI Dynamic Icon Image based on condition (New Card Visual)

    Power BI Dynamic Icon Image based on condition (New Card Visual)

    Did you know you can dynamically show images based on the numerical values with the new card visual. Created this whole…

    7 条评论
  • Distribution Chart Power BI with Gradient Color Using Dax

    Distribution Chart Power BI with Gradient Color Using Dax

    This is simply the combination of Matrix and Scatter plot in Power BI. Problem To create this scatter plot, need…

    2 条评论
  • Power BI Highlighted line based on selection

    Power BI Highlighted line based on selection

    Colors are a powerful tool in visual communication, but too much of a good thing can be overwhelming. This is…

    6 条评论
  • PowerBI Left-Anti Join using DAX(Dynamic)

    PowerBI Left-Anti Join using DAX(Dynamic)

    You can directly use left-anti join in power query, but it won't change with selection on filter. I came across a…

社区洞察

其他会员也浏览了