Show two Bar Charts side by Side one for current Month and the other for Previous Month

[BlueWhite3699]

Hi Experts

See sample data

https://www.dropbox.com/scl/fi/mvyt6d5qc9rfb6f40osqj/BlankPBIX.pbix?rlkey=6ac5yy4zpeyi0aru7v6vj5zav&... 

It is possible to show two bar charts on the same page one for currentr month if the user selects on the slicer Date Range 01.10.2023 to 31.10.2023 and under neath a second bar chart to Show data for 01.09.2023 to 30.09.2023 with September on the x -axis..

[BlueWhite3699]

Hi Post Patroin - Excellent feedback but how would i get the days in the month for all of September or any previous month

[FBergamaschi · Accepted solution]

PLEASE after you check my solution again and before marking it as a solution, can you repost this post on the DAX forum where it should belong and cancel from here? i shall reanswer there

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands

To see the dates of september

Like this

file is again on the folder, updated

https://drive.google.com/drive/folders/17O7VMx87jFT7kx_U18aKEeVHBNxHJKi_?usp=sharing

Again please repost on the DAX section (and cancel the post form here) and in case accept as a solution there, once I reanswer

[DataNinja777]

Hi @BlueWhite3699 ,

Yes, this is entirely possible to set up. The solution is to create a specific DAX measure that calculates the values for the previous month, which you'll use in your second bar chart. This allows the first chart to react normally to your date slicer while the second chart intelligently displays data from the prior period.

First, you need to create the new measure. In the Home or Modeling tab of Power BI, select New measure and enter the following DAX formula. This measure will calculate the electricity sum for the month immediately preceding the date range selected in your slicer.

Previous Month Electricity = 
CALCULATE(
    SUM('Electricity'[Electricity (khw)]),
    DATEADD('Date'[Date], -1, MONTH)
)

This DAX formula works by using CALCULATE to modify the filter context of the visual. It takes the base calculation, SUM('Electricity'[Electricity (khw)]), and applies a new filter, DATEADD('Date'[Date], -1, MONTH). The DATEADD function takes the dates currently selected in your slicer and shifts that entire period back by one month, effectively forcing the SUM to operate on the previous month's data regardless of the slicer's selection.

To build the report, add a date slicer to your page using the 'Date'[Date] field. Then, create your first bar chart for the current month by using 'Date'[Date] on the x-axis and the standard Electricity (khw) sum on the y-axis. For the second chart, use the same 'Date'[Date] field for the x-axis, but for the y-axis, use your new [Previous Month Electricity] measure. When you select a range like October in the slicer, the top chart will show October's data, and the bottom chart will correctly display September's data.

It is important to note that the x-axis labels on your "Previous Month" chart will still show the dates from the current selection (e.g., October dates), even though the bar values correspond to the previous month. This happens because the axis is filtered directly by the slicer, while only the measure's value is altered by CALCULATE. To avoid confusion, you should give the second chart a clear title, such as "Previous Month's Consumption."

Best regards,

[FBergamaschi]

Hi,

fixed a coupld of things before starting

1 - turned the relationship between the two tables into a one to many mondirectional (from date to electricity)

2 - turned off auto date/time (it can lead to issues)

That said, here is the result, does that work?

here the file with solution implemented

https://drive.google.com/drive/folders/17O7VMx87jFT7kx_U18aKEeVHBNxHJKi_?usp=sharing

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[BlueWhite3699]

Post Patron - thanks can you kindly share a file please

[FBergamaschi]

Please let me know if this is a solution

best

[FBergamaschi]

already in the previous post but here for convenience

https://drive.google.com/drive/folders/17O7VMx87jFT7kx_U18aKEeVHBNxHJKi_?usp=sharing

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI