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!