Forum Discussion

Tobi_Beginner's avatar
Tobi_Beginner
Frequent Visitor
2 years ago

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, ...
Need Help
dharmendars007's avatar
dharmendars007
Icon for Memorable Member rankMemorable Member
2 years ago

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's avatar
Tobi_Beginner
Frequent Visitor
2 years ago

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

 

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!