Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Tobi_Beginner
Frequent Visitor

Sum to specific date and dynamic filter

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!

3 REPLIES 3
Tobi_Beginner
Frequent Visitor

Screenshot 2024-09-22 161119.pngScreenshot 2024-09-22 160438.pngScreenshot 2024-09-22 161438.png

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
Super User
Super User

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 

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!

 

KundennameKundenartSparten KeyProduct KeyInsurerNWPTax %DIM_GWPCommission %Commisssion per yearVertragsbeginnVertragsablaufStatusSumme Vermittler-FP-%Summe Vermittler-AP-%Reporting DateAgencie
Customer APrivateSparte AProduct AInsurer A2102,0402102,040001.01.200601.10.2030Text0001.05.2024Test
Customer BCommercialSparte BProduct BInsurer B1395,5191660,6411153,509.11.201501.01.2025Text0001.05.2024Test
Customer CPrivateSparte AProduct BInsurer E641,4419763,3125160,3601.10.201201.10.2024Text0001.05.2024Test
Customer DPrivateSparte AProduct CInsurer D239,9319285,522559,9901.06.202401.06.2025Text0001.06.2024Test
Customer EPrivateSparte EProduct CInsurer C651977,35106,511.08.201501.01.2025Text0001.06.2024Test
Customer ICommercialSparte BProduct EInsurer A1468,16191747,1110,5154,1620.07.202201.01.2025Text0001.06.2024Test
Customer JCommercialSparte BProduct AInsurer A88,519105,32119,7411.03.202201.01.2025Text0001.08.2024Test
Customer MCommercialSparte EProduct BInsurer B55,41965,932513,8515.03.202215.03.2025Text0001.08.2024Test
Customer NCommercialSparte BProduct AInsurer B1066,92191269,6411117,3611.03.202201.01.2025Text0001.08.2024Test
Customer OPrivateSparte BProduct AInsurer B77,711992,48118,5506.01.202301.01.2025Text0001.08.2024Test
Customer PCommercialSparte EProduct CInsurer D347,2419413,2223,581,604.02.202201.01.2025Text0001.08.2024Test
Customer CPrivateSparte BProduct BInsurer D656,5219781,26959,0911.08.201701.01.2025Text0001.07.2024Test
Customer PPrivateSparte BProduct BInsurer D68019809,210,7372,9606.06.201701.01.2025Text0001.07.2024Test

 

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

 

Screenshot 2024-09-23 100109.png

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!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.