Sum to specific date and dynamic filter

[Tobi_Beginner]

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!

[Tobi_Beginner]

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!

[dharmendars007]

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

LinkedIN 

[Tobi_Beginner]

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:

1.  'Fn_Calendar[Year #] and 'Fn_Calendar [Month M] is active
   sum 'Customer Base Agencies[DIM_GWP] to previous month of filterd date
   
2.  'Fn_Calendar [Month M] is active
   sum 'Customer Base Agencies[DIM_GWP] to previous month of filterd month and year of specific date (2024)
3. 'Fn_Calendar[Year #] is active
   sum 'Customer Base Agencies[DIM_GWP] of previous year to filterd year

I hope this post makes it easier to understand my goal. I would be very grateful for any help.
Thank you in advance!