Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hi all,
I am still a beginner and relatively new to Power BI. I have been trying for a while to solve the following scenario.
I want to calculate a sum of sales so that, when the report is opened, the sum is based by default on the previous month from a specific date (for example: 01.08.2024).
Additionally, I have filters for month and year in my report.
As soon as these filters are used (either month only or both), the sum should be calculated for the previous month of the selected date via the filter.
If only the year filter is active, the sum should be calculated for the previous year.
The fact table is in Excel and the main columns consist of the Reporting Date (Format: Monday, 1. January 2024) and several sales rows (Revenue, Profit, Number of Sales..). There are several thousand rows, each with a date representing the 1st of a month (1. January 2024, 1. February 2024, ...).
The filters Jahr # and Monat M are attributes from a date dimension table.
I just can’t get it to work at the moment and would be very grateful for any help.
Thank you in advance!
By default, the value 3,623 should be displayed in this case (for 01.08.2024).
When the month filter and year filter, or only the month for example, July (2024), are applied, the result 5,184.51 should be shown in the card visual.
When only the year is filtered to 2025, the result should be 13,011.81 in this example.
I hope this makes it clearer.
Thank you very much!
Hello @Tobi_Beginner ,
Request you to share the sample data which will help us to process further..and also expected result.
If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!
Thank You
Dharmendar S
Hi,
after I've read some "How to provide example data" etc.
Here is a new attempt on my part to provide you with the necessary data and information correctly and in a way that works for you. Please bear with me and give me feedback if you're unable to work with this. Many thanks!
Kundenname | Kundenart | Sparten Key | Product Key | Insurer | NWP | Tax % | DIM_GWP | Commission % | Commisssion per year | Vertragsbeginn | Vertragsablauf | Status | Summe Vermittler-FP-% | Summe Vermittler-AP-% | Reporting Date | Agencie |
Customer A | Private | Sparte A | Product A | Insurer A | 2102,04 | 0 | 2102,04 | 0 | 0 | 01.01.2006 | 01.10.2030 | Text | 0 | 0 | 01.05.2024 | Test |
Customer B | Commercial | Sparte B | Product B | Insurer B | 1395,5 | 19 | 1660,64 | 11 | 153,5 | 09.11.2015 | 01.01.2025 | Text | 0 | 0 | 01.05.2024 | Test |
Customer C | Private | Sparte A | Product B | Insurer E | 641,44 | 19 | 763,31 | 25 | 160,36 | 01.10.2012 | 01.10.2024 | Text | 0 | 0 | 01.05.2024 | Test |
Customer D | Private | Sparte A | Product C | Insurer D | 239,93 | 19 | 285,52 | 25 | 59,99 | 01.06.2024 | 01.06.2025 | Text | 0 | 0 | 01.06.2024 | Test |
Customer E | Private | Sparte E | Product C | Insurer C | 65 | 19 | 77,35 | 10 | 6,5 | 11.08.2015 | 01.01.2025 | Text | 0 | 0 | 01.06.2024 | Test |
Customer I | Commercial | Sparte B | Product E | Insurer A | 1468,16 | 19 | 1747,11 | 10,5 | 154,16 | 20.07.2022 | 01.01.2025 | Text | 0 | 0 | 01.06.2024 | Test |
Customer J | Commercial | Sparte B | Product A | Insurer A | 88,5 | 19 | 105,32 | 11 | 9,74 | 11.03.2022 | 01.01.2025 | Text | 0 | 0 | 01.08.2024 | Test |
Customer M | Commercial | Sparte E | Product B | Insurer B | 55,4 | 19 | 65,93 | 25 | 13,85 | 15.03.2022 | 15.03.2025 | Text | 0 | 0 | 01.08.2024 | Test |
Customer N | Commercial | Sparte B | Product A | Insurer B | 1066,92 | 19 | 1269,64 | 11 | 117,36 | 11.03.2022 | 01.01.2025 | Text | 0 | 0 | 01.08.2024 | Test |
Customer O | Private | Sparte B | Product A | Insurer B | 77,71 | 19 | 92,48 | 11 | 8,55 | 06.01.2023 | 01.01.2025 | Text | 0 | 0 | 01.08.2024 | Test |
Customer P | Commercial | Sparte E | Product C | Insurer D | 347,24 | 19 | 413,22 | 23,5 | 81,6 | 04.02.2022 | 01.01.2025 | Text | 0 | 0 | 01.08.2024 | Test |
Customer C | Private | Sparte B | Product B | Insurer D | 656,52 | 19 | 781,26 | 9 | 59,09 | 11.08.2017 | 01.01.2025 | Text | 0 | 0 | 01.07.2024 | Test |
Customer P | Private | Sparte B | Product B | Insurer D | 680 | 19 | 809,2 | 10,73 | 72,96 | 06.06.2017 | 01.01.2025 | Text | 0 | 0 | 01.07.2024 | Test |
Table Relationships:
Carrier_Agencies Customer Base Agencies
Carrier *:*Insurer
Customer Base Agencies DIM_Agencies
Agencie *:1 Agencies
Customer Base Agencies Fn_Calendar
Reporting Date *:1 Datum
Sparten Agencies Custimer Base Agencies
Sparten Kürzel *:* Sparten Key
I have been trying for a while to solve the following scenario:
Sum the DIM_GWP colum for previous month by specific date.
I have 2 tables (Customer-Base_agencies, Fn_Calendar) and 2 Filters on 'Fn_Calendar[Year #] and 'Fn_Calendar [Month M]
I want to produce a DAX_Formel for a Visualisation that shows by default the sum of 'Customer Base Agencies[DIM_GWP] for a specific date. In the first step for the date: 01.08.2024. (It should be possible to switch specific date manually)
By using the Filters I want to show the sum of the 'Customer Base Agencies[DIM_GWP] by selected date of the filters with folling logic:
I hope this post makes it easier to understand my goal. I would be very grateful for any help.
Thank you in advance!